Best practices for writing
MySQL queries
Tips, tricks, and best practices to apply
when writing MySQL queries
SQL REPLACE 01
SQL UPDATE with JOIN 03
SQL INSERT INTO SELECT 05
SQL DELETE 08
MySQL Index Examples 09
01
SQL REPLACE
5 tips for finding and replacing text in MySQL
1 SQL REPLACE replaces text
for ALL occurrences 2 SQL REPLACE can remove texts
-- Replace all occurrences of 'know' within a given string
USE sakila;
SELECT email,
SELECT REPLACE('know the unknown','know','foresee');
REPLACE(email,'.','_') AS newemail
-- Output: 'foresee the unforeseen'
FROM customer;
-- Add a space to the string to search for entire words only
-- Output:
MARY.SMITH@sakilacustomer.org
SELECT REPLACE('know the unknown','know ','foresee');
MARY_SMITH@sakilacustomer_org
-- Output: 'foresee the unknown'
PATRICIA.JOHNSON@sakilacustomer.org
PATRICIA_JOHNSON@sakilacustomer_org
3 Collations affect the results
of SQL REPLACE
-- REPLACE in MySQL is case- and language-sensitive.
SET @string := 'Cats are great pets and so easy to take care of. They make good companions. Having a cat around is good for children.';
SELECT REPLACE(@string,'cat','dog');
-- Output: Cats are great pets and so easy to take care of. They make good companions. Having a dog around is good for children.
-- If you capitalize the word "cat" in the query (i.e., "Cat"), the replace operation will not commence.
SELECT REPLACE(@string,'Cat','dog');
-- Output: dogs are great pets and so easy to take care of. They make good companions. Having a cat around is good for children.
02
4 SQL REPLACE can be nested
DECLARE @string = 'Cats are great pets and so easy to take care of. They make good companions. Having a cat around is good for children.';
SELECT REPLACE(REPLACE(@string,'cat','dog'),'Cat','Dog');
-- Output: Dogs are great pets and so easy to take care of. They make good companions. Having a dog around is good for children.
5 REPLACE can be used with UPDATE to store replaced texts
-- Update the customer table by replacing sakilacustomer.org with a fictitious email:
UPDATE customer
SET email = REPLACE(email,'sakilacustomer.org','sakila.gmail.com')
WHERE last_update > '2020-10-01';
03
1 Handle errors (SQL EXCEPTION) and transactions
(START TRANSACTION)
-- Service script:
ALTER TABLE customer
ADD COLUMN rating VARCHAR(20) DEFAULT NULL;
-- When an error of any kind occurs, the operation will roll back,
and the table data will not be updated.
SQL UPDATE with JOIN
DELIMITER $$
3 tips for running SQL UPDATE CREATE DEFINER = 'root'@'%'
with JOIN in MySQL PROCEDURE InsertAggregate (IN incst int UNSIGNED)
BEGIN
DECLARE ct int;
DECLARE sm decimal(5, 2);
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT 'An error has occurred, the operation has been
rolled back, and the stored procedure has been
terminated';
END;
START TRANSACTION;
SELECT SUM(amount), COUNT(*)
FROM payment p
WHERE customer_id = incst INTO sm, ct;
UPDATE customer
SET summary = sm, avg_summary = sm / ct
WHERE customer_id = incst;
UPDATE payment
SET avg_flag = 1
WHERE customer_id = incst;
COMMIT;
END
$$
DELIMITER ;
04
2 Preview records using the SELECT statement 3 Use START TRANSACTION
-- With the SELECT statement, you can view the required records
prior to the actual update.
-- When an error of any kind occurs, the operation will roll back,
and the table data will not be updated.
SELECT c.*, r.rating FROM customer c,
(SELECT customer_id, SUM(amount),
SET @cst:=1;
CASE
START TRANSACTION;
WHEN SUM(amount) > 120 THEN 5
WHEN SUM(amount) BETWEEN 120 AND 110 THEN 4
SELECT SUM(amount), SUM(amount)/COUNT(*)
WHEN SUM(amount) BETWEEN 109 AND 100 THEN 3
FROM payment p
WHEN SUM(amount) BETWEEN 99 AND 90 THEN 2
WHERE customer_id = @cst /* AND avg_flag = 1*/
WHEN SUM(amount) > 90 THEN 1
INTO @sm, @ct;
ELSE 'No Rating'
END rating
UPDATE customer
FROM payment GROUP BY customer_id) r
SET avg_summary=@sm/@ct
WHERE c.customer_id = r.customer_id;
WHERE customer_id = @cst;
UPDATE payment
SET avg_flag = 1
UPDATE customer c JOIN
WHERE customer_id = @cst;
(SELECT customer_id, SUM(amount),
CASE
COMMIT;
WHEN SUM(amount) > 120 THEN 5
WHEN SUM(amount) BETWEEN 120 AND 110 THEN 4
WHEN SUM(amount) BETWEEN 109 AND 100 THEN 3
WHEN SUM(amount) BETWEEN 99 AND 90 THEN 2
-- Service script:
WHEN SUM(amount) > 90 THEN 1
ELSE 'No Rating'
ALTER TABLE customer
END rating
ADD COLUMN summary decimal(5, 2) DEFAULT NULL,
FROM payment GROUP BY customer_id) r
ADD COLUMN avg_summary decimal(5, 2) DEFAULT NULL;
ON c.customer_id = r.customer_id
SET c.rating = r.rating; ALTER TABLE payment
ADD COLUMN avg_flag INT DEFAULT NULL;
05
SQL INSERT INTO
SELECT
The easiest ways to handle duplicates
1 Use INSERT INTO SELECT DISTINCT
-- Create a test table with duplicate records:
CREATE TABLE rental_double AS
SELECT * FROM rental;
INSERT INTO rental_double
SELECT * FROM rental;
-- Create a result table:
INSERT INTO rental_no_double AS
SELECT DISTINCTROW rental_id, rental_date, inventory_id, customer_id, return_date, staff_id, last_update
FROM rental_double rd;
-- OR
INSERT INTO rental_no_double AS
SELECT DISTINCT rental_id, rental_date, inventory_id, customer_id, return_date, staff_id, last_update
FROM rental_double rd;
06
2 Use INSERT IGNORE and PRIMARY KEY/UNIQUE INDEX
-- Create a result table with PRIMARY KEY (rental_id) or UNIQUE INDEX (rental_id):
CREATE TABLE jordan.rental_no_double (
rental_id int NOT NULL,
rental_date datetime NOT NULL,
inventory_id mediumint UNSIGNED NOT NULL,
customer_id smallint UNSIGNED NOT NULL,
return_date datetime DEFAULT NULL,
staff_id tinyint UNSIGNED NOT NULL,
last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (rental_id));
-- Use INSERT IGNORE - duplicates will not be added during insertion:
INSERT IGNORE INTO rental_no_double
SELECT rental_id, rental_date, inventory_id,
customer_id, return_date, staff_id, last_update
FROM rental_double;
3 Use GROUP BY
INSERT INTO rental_double
SELECT rental_id, rental_date, inventory_id,
customer_id, return_date, staff_id, last_update
GROUP BY (rental_id, rental_date, inventory_id,
customer_id, return_date, staff_id, last_update);
07
4 Use WHERE IN
-- Create a test table with an extra column and duplicate records:
SET @id:=0;
CREATE TABLE rental_double as
5 Use HAVING COUNT(*) > 1
SELECT @id:=@id+1 AS id, rental_id, rental_date, inventory_id,
-- Create a test table with an extra column and duplicate records:
customer_id, return_date ,staff_id, last_update
FROM rental;
SET @id:=0;
INSERT INTO rental_double
CREATE TABLE rental_double as
SELECT @id:=@id+1 AS id, rental_id, rental_date, inventory_id,
SELECT @id:=@id+1 AS id, rental_id, rental_date,
customer_id, return_date ,staff_id, last_update
inventory_id, customer_id, return_date,
FROM rental;
staff_id, last_update
FROM rental;
INSERT INTO rental_double
INSERT INTO rental_no_double
SELECT @id:=@id+1 AS id, rental_id, rental_date,
SELECT id, rental_id, rental_date, inventory_id,
inventory_id, customer_id, return_date,
customer_id, return_date, staff_id, last_update
staff_id, last_update
FROM rental_double
FROM rental;
WHERE ID IN (SELECT MAX(id) FROM rental_double
GROUP BY rental_id, rental_date, inventory_id,
customer_id, return_date, staff_id, last_update
HAVING COUNT(rental_id) > 1);
INSERT INTO rental_no_double
SELECT MAX(id) AS id, rental_id, rental_date,
inventory_id, customer_id, return_date,
staff_id, last_update
FROM rental_double
GROUP BY rental_id, rental_date, inventory_id,
customer_id, return_date, staff_id,
last_update
HAVING COUNT(*) > 1;
08
SQL DELETE 3 DELETE with a subquery
5 useful DELETE syntax tips for DELETE p FROM payment p
MySQL developers
?
WHERE p.customer_id = 2
AND p.staff_id IN (SELECT s.staff_id
FROM staff s
WHERE s.store_id IN (1,2)
AND s.username LIKE 'J%');
1 Delete all records 4 Limit
CREATE TABLE payment_backup AS
-- Delete 3 sorted records at a time:
SELECT * FROM payment;
DELETE FROM payment
DELETE FROM payment_backup;
WHERE customer_id = 3
ORDER BY payment_id, payment_date DESC
LIMIT 3;
2 Conditional DELETE with the WHERE clause 5 DELETE with a JOIN
-- Delete 1 record using the equality operator:
DELETE p FROM payment p
INNER JOIN staff s
DELETE FROM payment
ON s.staff_id = p.staff_id
WHERE payment_id = 150;
WHERE p.customer_id = 2
AND s.store_id IN (1,2)
-- Delete 3 records using IN:
AND s.username LIKE 'J%';
DELETE FROM payment
WHERE payment_id IN (151,152,153);
09
MySQL Index Examples
16 index examples to speed up your
SQL queries
1 Add a clustered index within CREATE TABLE
-- A clustered index is created along with the primary key:
CREATE TABLE payment (
payment_id smallint UNSIGNED NOT NULL AUTO_INCREMENT,
customer_id smallint UNSIGNED NOT NULL,
staff_id tinyint UNSIGNED NOT NULL,
3 Create a unique index that includes column prefix key parts
rental_id int DEFAULT NULL,
-- Using CREATE INDEX:
amount decimal(5, 2) NOT NULL,
payment_date datetime NOT NULL,
CREATE UNIQUE INDEX UK_customer ON customer(email (45));
last_update timestamp NULL
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (payment_id))
ENGINE = INNODB; -- Using ALTER TABLE:
ALTER TABLE customer
ADD UNIQUE INDEX UK_customer (email (45));
2 MySQL automatically creates an index for a Foreign Key
ALTER TABLE payment
ADD CONSTRAINT fk_payment_rental FOREIGN KEY (rental_id)
4 Create a filtered index
REFERENCES rental (rental_id) ON DELETE
ALTER TABLE customer
SET NULL ON UPDATE CASCADE; ADD INDEX idx_last_name (last_name);
10
5 Create a spatial index 6 Create a hash index
-- Using CREATE TABLE:
ALTER TABLE data_dict
ADD INDEX data_dict_dn USING HASH (data_name);
CREATE TABLE address (
7 Create a new index—and drop it immediately if it exists
address_id smallint unsigned NOT NULL AUTO_INCREMENT,
address varchar(50) NOT NULL,
address2 varchar(50) DEFAULT NULL,
district varchar(20) NOT NULL,
city_id smallint unsigned NOT NULL,
postal_code varchar(10) DEFAULT NULL,
ALTER TABLE address
phone varchar(20) NOT NULL,
DROP INDEX idx_location,
location geometry NOT NULL,
ADD INDEX idx_location (location);
last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (address_id),
8 Use multi-valued indexes
SPATIAL INDEX idx_location (location));
-- Using ALTER TABLE:
-- Using CREATE TABLE only:
CREATE TABLE address (
address_id smallint unsigned NOT NULL AUTO_INCREMENT,
CREATE TABLE customers (
address varchar(50) NOT NULL,
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
address2 varchar(50) DEFAULT NULL,
modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
district varchar(20) NOT NULL,
custinfo JSON,
city_id smallint unsigned NOT NULL,
INDEX zips((CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY))));
postal_code varchar(10) DEFAULT NULL,
phone varchar(20) NOT NULL,
location geometry NOT NULL,
last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-- Using CREATE TABLE and ALTER TABLE:
PRIMARY KEY (address_id),
SPATIAL INDEX idx_location (location));
CREATE TABLE customers (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
ALTER TABLE address
modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
ADD SPATIAL INDEX idx_location (location);
custinfo JSON);
-- Using CREATE INDEX:
ALTER TABLE customers
ADD INDEX zips((CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)));
CREATE TABLE address (
address_id smallint unsigned NOT NULL AUTO_INCREMENT,
address varchar(50) NOT NULL,
address2 varchar(50) DEFAULT NULL,
-- Using CREATE TABLE and CREATE INDEX:
district varchar(20) NOT NULL,
city_id smallint unsigned NOT NULL,
CREATE TABLE customers (
postal_code varchar(10) DEFAULT NULL,
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
phone varchar(20) NOT NULL,
modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
location geometry NOT NULL,
custinfo JSON);
last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (address_id),
SPATIAL INDEX idx_location (location));
CREATE INDEX zips
CREATE SPATIAL INDEX idx_location ON address (location); ON customers ((CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)));
11
9 Create a unique composite index 10 Create a composite index with a multi-valued part
ALTER TABLE address
CREATE TABLE customers (
ADD UNIQUE INDEX UK_address (address, phone); id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
custinfo JSON);
11 Add a composite index
ALTER TABLE customers
ALTER TABLE customer
ADD INDEX comp(id, modified,
ADD INDEX idx_customer (first_name, email); (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)));
12 Add an index within CREATE TABLE using CONSTRAINT
CREATE TABLE address (
address_id smallint unsigned NOT NULL AUTO_INCREMENT,
address varchar(50) NOT NULL,
address2 varchar(50) DEFAULT NULL,
district varchar(20) NOT NULL,
city_id smallint unsigned NOT NULL,
postal_code varchar(10) DEFAULT NULL,
phone varchar(20) NOT NULL,
location geometry NOT NULL,
last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (address_id),
KEY idx_fk_city_id (city_id),
CONSTRAINT fk_address_city FOREIGN KEY (city_id) REFERENCES city (city_id) ON DELETE RESTRICT ON UPDATE CASCADE);
12
13 Drop an index using IF EXISTS
DELIMITER $$
CREATE PROCEDURE `DropIndexIfExists`(
IN i_table_name VARCHAR(128),
IN i_index_name VARCHAR(128))
BEGIN
SET @tableName = i_table_name;
SET @indexName = i_index_name;
SET @indexExists = 0;
SELECT 1
INTO @indexExists
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_NAME = @tableName
AND INDEX_NAME = @indexName;
14 Drop an index
SET @query = CONCAT('DROP INDEX ', @indexName, ' ON ', @tableName); -- Using ALTER TABLE:
IF @indexExists THEN
PREPARE stmt FROM @query;
ALTER TABLE address
EXECUTE stmt;
DROP INDEX idx_location;
DEALLOCATE PREPARE stmt;
END IF;
-- Using DROP INDEX:
END
$$
DROP INDEX idx_location
DELIMITER; ON address;
15 Create a full-text index
ALTER TABLE address
ADD
WITH
FULLTEXT INDEX IDX_address_address (address)
PARSER NGRAM;
16 Rename an index
ALTER TABLE address
ADD FULLTEXT INDEX IDX_address_address2 (address2) ALTER TABLE address
WITH PARSER MECAB; RENAME INDEX idx_location TO idx_location1;
13
dbForge Studio for MySQL:
your comprehensive IDE for MySQL databases
dbForge Studio is a multifunctional IDE SQL editor
The built-in SQL editor helps you effectively manage your SQL code with
for MySQL, which helps you handle a smart completion, syntax highlighting, formatting, refactoring, and a slew of
other productivity enhancements that let you get a sharper focus on your
variety of tasks, including operations work.
with JOINs. Context-sensitive code completion
With its help, you can instantly get With dbForge Studio, you can easily speed up your routine SQL coding by at
least 90% with context-sensitive keyword and object suggestions, which
at least 30% more effective and reduce include auto-generation of JOIN clauses. Auxiliary features include code
the time you typically spend on your snippets, column picker, wildcards, highlighting, and parameter information.
routine work with databases Instant syntax check
by about 50%. The syntax checker is one of the most valuable features integrated into the
SQL editor. Whenever the checker detects an error in the code that you are
typing, it instantly highlights the problematic place so you can fix it
immediately.
SQL formatting
It is easy to improve the readability, consistency, and standardization of your
DOWNLOAD FREE 30-DAY TRIAL code with the rich SQL formatting options offered by the Studio. Depending
on your needs, you can apply automatic, manual, or wizard-aided formatting.
14
The built-in MySQL Debugger empowers you
with automated code debugging capabilities
and makes it no longer necessary to search
for errors in your scripts manually. Its sub-
features include step-by-step execution, call
stack browsing, breakpoints, and watches.
With its help, you can easily investigate the
run-time behavior of your database objects,
locate logic errors, and debug code directly in
your IDE. This helps you avoid performance
issues and minimize possible downtime.
I WANT TO TRY!
15
Helpful resources
Here are a few bonuses to help you expand your MySQL skills.
Beginner's guide to Helpful resources to boost More MySQL insights
dbForge Studio your skills with dbForge on our blog
Studio
Get started with dbForge Studio for free today! DOWNLOAD FREE 30-DAY TRIAL
