Master MySQL: 14 Essential Functions and Commands Every Developer Should Know

Preface

I have been using MySQL for years and discovered many useful tricks that I want to share.

1. group_concat

When you need to concatenate values from rows with the same group, use the group_concat function. select name from `user` group by name; To concatenate the code values for each name:

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

The result shows the values joined by commas.

2. char_length

Use char_length to get the length of a string and sort by it.

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

This sorts names by their character length.

3. locate

To find the position of a substring, use locate.

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

If lengths are equal, rows are ordered by the position of the keyword.

4. replace

Replace parts of a string with replace. Example replacing 'A' with 'B':

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

You can also remove spaces or modify JSON content using the same function.

5. now

Get the current timestamp with now(). For milliseconds, use now(3):

select now() from brand limit 1;

select now(3) from brand limit 1;

6. insert into ... select

Insert data from another table efficiently:

INSERT INTO `brand`(id,code,name,edit_date) select null,code,name,now(3) from `order` where code in ('004','005');

7. insert into ... ignore

Use INSERT IGNORE to avoid errors when a unique key already exists:

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

8. select ... for update

Apply a pessimistic lock to a row within a transaction:

begin;<br/>select * from `user` where id=1 for update;<br/>-- business logic<br/>update `user` set score=score-1 where id=1;<br/>commit;

The lock works only when the WHERE clause uses a primary key or unique index.

9. on duplicate key update

Insert a row or update it if a duplicate key exists:

INSERT INTO `brand`(id,code,name,edit_date) VALUES (123,'108','苏三',now(3)) on duplicate key update name='苏三', edit_date=now(3);

Be cautious of potential deadlocks under high concurrency.

10. show create table

View the full CREATE TABLE statement, including indexes and engine details:

show create table `order`;

11. create table ... select

Create a new table and populate it in one step:

create table order_2022121820 select * from `order`;

12. explain

Use explain to see the execution plan and index usage of a query:

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

13. show processlist

Inspect current threads and identify long‑running or problematic queries:

show processlist;

14. mysqldump

Export a database or table to a SQL file for backup or migration:

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