Useful MySQL Functions and SQL Techniques for Data Manipulation

Hello, I am Su San, back again to share a set of handy MySQL tricks I have gathered over recent years.

1. GROUP_CONCAT – combines values of rows that share the same group. Example:

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;

The result shows each name with its codes joined by commas.

2. CHAR_LENGTH – returns the length of a string, useful for ordering. Example:

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

This orders rows by the character count of name .

3. LOCATE – finds the position of a substring. Example:

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

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

Other similar functions are INSTR and POSITION .

4. REPLACE – substitutes part of a string. Example:

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

It can also remove spaces or modify JSON content.

5. NOW – returns the current timestamp. Simple usage:

select now() from brand limit 1;

To include milliseconds:

select now(3) from brand limit 1;

6. INSERT … SELECT – inserts rows produced by a SELECT query, ideal for bulk operations. Example:

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

7. INSERT … IGNORE – inserts a row unless a unique‑key conflict occurs, in which case the statement is ignored. Example:

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

Alternatively, a conditional insert can be written with NOT EXISTS , but INSERT … IGNORE is simpler.

8. SELECT … FOR UPDATE – acquires a pessimistic lock on selected rows within a transaction. Example:

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

The row is locked until the transaction commits, preventing concurrent modifications.

9. ON DUPLICATE KEY UPDATE – inserts a row, or updates it if a primary‑key or unique‑key conflict occurs. Example:

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 – displays the full CREATE statement of a table, including columns, indexes, engine, and charset. Example:

show create table `order`;

This provides a complete view of the table definition.

11. CREATE TABLE … SELECT – creates a new table and populates it in a single step. Example:

create table order_2022121820 select * from `order`;

The new table is created with the same structure and data as the source.

12. EXPLAIN – shows the execution plan of a query, revealing index usage. Example:

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

The output helps diagnose why an index may not be used.

13. SHOW PROCESSLIST – lists active threads, their status, and the SQL they are running, useful for spotting long‑running or stuck queries.

14. mysqldump – command‑line utility for exporting a database or table to a SQL file. Example:

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

The generated file can later be imported to restore data.

Finally, if you found this article helpful, please follow the author’s public account for more technical content.