Master MySQL: 13 Essential Functions and Commands for Efficient Data Handling

1. group_concat

Use group_concat to concatenate values from rows that share the same grouping key into a single comma‑separated string.

select name from `user` group by name;

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

2. char_length

Retrieve the character length of a string with char_length and use it for ordering results.

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

3. locate

Find the position of a substring within a string using locate. 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 similar functions include instr and position.

4. replace

Replace parts of a string with replace. It can also be used to trim spaces or modify JSON strings.

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

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

5. now

Retrieve the current timestamp with now(). Adding a precision argument returns milliseconds.

select now() from brand limit 1;

select now(3) from brand limit 1;

6. insert into … select

Insert large batches of data derived from another table using insert into … select.

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

Prevent duplicate‑key errors by using insert ignore, which silently skips rows that would violate a unique index.

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

8. select … for update

Apply a pessimistic lock on selected rows within a transaction using select … for update. The lock works only when the WHERE clause uses 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

Combine insert and update in one statement: if the primary/unique key exists, the row is updated; otherwise it is inserted.

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 potential deadlocks under high concurrency.

10. show create table

Display the full CREATE TABLE statement, which includes column definitions, indexes, engine, charset, etc.

show create table `order`;

11. create table … select

Create a new table and populate it in a single command.

create table order_2022121820 select * from `order`;

12. explain

View the execution plan of a query with explain to understand index usage and potential performance issues.

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

13. show processlist

Inspect current MySQL threads, their state, and running queries. Useful for diagnosing long‑running or stuck statements.

show processlist;

14. mysqldump

Export database tables to a SQL file for backup or migration using the mysqldump utility.

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