MySQL

Updated: 10 September 2022

User defined variables can be assigned a value in one statement and referred to in another statement.

User variables are written as @var_name

For SET, either = or := can be used as the assignment operator.

User variables can be assigned a value from a limited set of data types: integer, decimal, floating-point, binary or nonbinary string, or NULL.

One way to set a user-defined variable is by issuing a SET statement

SET @var_name = expr [, @var_name = expr] ...

Also see SELECT ... INTO statement.

Updated: 28 August 2026

Show columns statement

SHOW COLUMNS FROM City;

Query for big tables

SELECT 
     table_schema as `Database`, 
     table_name AS `Table`, 
     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
WHERE table_schema = "my_table"
ORDER BY (data_length + index_length) DESC;

Disable foreign key checks

SET foreign_key_checks = 0;
SET foreign_key_checks = 1;

log_bin_trust_function_creators
SQLSTATE[HY000]: General error: 1418 This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

select @@log_bin_trust_function_creators;
SET GLOBAL log_bin_trust_function_creators = 1;
select @@log_bin_trust_function_creators;

Show the string which can be used to re-create the named stored function

SHOW CREATE FUNCTION f_gross_store_income;

Show the string which can be used to re-create the named stored procedure

SHOW CREATE PROCEDURE sp_rent_chart;

Returns a string that indicates the MySQL server version

SELECT VERSION();

UNION ALL

select * from (select 'a' union select 'b') p
union all
select * from (select 'a' union select 'b') q

-- a
-- b
-- a
-- b

Format a date

SELECT date_format("2017-06-15 00:00:00", "%Y-%m-%d");
-- 2017-06-15

Find a column with substring last_trade in it’s name

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'database_name' 
AND COLUMN_NAME LIKE '%last_trade%';