MySQL tips and tricks

Common queries:

Copy data from one tabe to another

Copy data from one tabe to a new table

Copy data from one tabe to another (update existed records)

Select latest one from each category (group-wise max in a group query)

Select N latest records in a group

Select N rows before and after the matching row

How to GROUP BY other

Select values that have at least ID from array

Select values that have all ID from array

Extract many-to-many to many-to-many in one query

Find and remove duplicates

Delete records from multiple tables with one query

Delete related records with one query

Get updated value from UPDATE statement without querying the table again

How to round TIME values to the nearest period – 10, 15, 20, 30 minutes.

How to generate random char.

How to select SUM or COUNT of rows with different condition.


Data Types:

How to edit values of SET data type


Stored routines:

Pass array to stored procedure

How to exit from stored procedure.


Ranking and ordering:

Rank records

Rank records (grouped ranking)

Custom ordering (order rows by my order)

Random row selection


Export and import data:

Export data to CSV file

Import data from CSV file

Import, change and format data from CSV file


Pivot tables:

Pivot tables (transform rows to columns)

Dynamic pivot tables (transform rows to columns)


Metadata, information:

How to set AUTO_INCREMENT step

Drop multiple tables named with prefix

MySQL server version