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: 11 February 2025

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