MySQL

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