13 Must‑Know MySQL Functions and Tricks for Everyday Development

Databases and SQL are essential skills for developers, yet many learn only the basics. This article presents a set of useful MySQL statements that go beyond simple CRUD operations.

1. GROUP_CONCAT

Use GROUP_CONCAT to concatenate values from rows that share the same group into a single comma‑separated string. select name from `user` group by name; To concatenate the code column for each name:

select name, group_concat(code) from `user` group by name;

Result:

2. CHAR_LENGTH

The CHAR_LENGTH function returns the length of a string, which can be used for sorting.

select * from brand where name like '%苏三%' order by char_length(name) asc limit 5;

Result (ordered by name length):

3. LOCATE (and INSTR / POSITION)

LOCATE(substr, str) returns the position of a substring. It can be combined with other ordering criteria.

select * from brand where name like '%苏三%' order by char_length(name) asc, locate('苏三', name) asc limit 5,5;

Other functions with similar behavior are INSTR and POSITION .

4. REPLACE

REPLACE(str, from, to) substitutes part of a string. It can also remove spaces or modify JSON text.

update brand set name = REPLACE(name, 'A', 'B') where id = 1;

update brand set name = REPLACE(name, ' ', '') where name like ' %';

5. NOW()

NOW() returns the current timestamp (date and time). Adding a precision argument returns milliseconds.

select now() from brand limit 1;

select now(3) from brand limit 1;

6. INSERT … SELECT

When inserting large amounts of data from another table, use the INSERT … SELECT syntax.

INSERT INTO `brand` (id, code, name, edit_date)
SELECT null, code, name, now(3) FROM `order` WHERE code IN ('004','005');

7. INSERT … IGNORE

INSERT IGNORE skips rows that would cause a duplicate‑key error.

INSERT IGNORE INTO `brand` (id, code, name, edit_date) VALUES (123, '108', '苏三', now(3));

8. SELECT … FOR UPDATE

Use SELECT … FOR UPDATE inside a transaction to lock the selected rows (requires a primary key or unique index).

BEGIN;
SELECT * FROM `user` WHERE id = 1 FOR UPDATE;
-- business logic
UPDATE `user` SET score = score - 1 WHERE id = 1;
COMMIT;

9. ON DUPLICATE KEY UPDATE

This statement inserts a row, but if a primary‑key or unique‑key conflict occurs, it updates the existing row.

INSERT INTO `brand` (id, code, name, edit_date) VALUES (123, '108', '苏三', now(3))
ON DUPLICATE KEY UPDATE name = '苏三', edit_date = now(3);

Be aware of possible deadlocks under high concurrency.

10. SHOW CREATE TABLE

To view the full DDL of a table, use SHOW CREATE TABLE . It displays column definitions, indexes, engine, charset, etc.

show create table `order`;

11. CREATE TABLE … SELECT

One command can create a new table and populate it from an existing one.

create table order_2022121820
select * from `order`;

12. EXPLAIN

Use EXPLAIN to view the execution plan and index usage of a query.

explain select * from `order` where code='002';

Common reasons for index loss are shown in the following diagram:

13. SHOW PROCESSLIST

To diagnose long‑running or stuck queries, run SHOW PROCESSLIST to see each thread’s status.

show processlist;

14. MYSQLDUMP

The mysqldump utility exports a database (or selected tables) as a series of INSERT statements, enabling backup and restore.

mysqldump -h 192.22.25.226 -u root -p123456 dbname > backup.sql