Tips¶

Change charset¶

ALTER TABLE `ci_session` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci

Show table engine¶

For a table given :

SHOW TABLE STATUS WHERE Name = 'xxx';

For all tables of a database dbname

SELECT TABLE_NAME,
       ENGINE
FROM   information_schema.TABLES
WHERE  TABLE_SCHEMA = 'dbname';

Error Code: 1005. Can’t create table database.table (errno: 150 “Foreign key constraint is incorrectly formed”)¶

Check if the reference of your foreign key has an index…

You can get the actual error message by running SHOW ENGINE INNODB STATUS; and then looking for LATEST FOREIGN KEY ERROR in the output.

Search if a field exist in all table of all databases¶

SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%wild%';

Add WHERE TABLE_SCHEMA="" for using a single database

Duplicate table¶

To copy with indexes and triggers do these 2 queries:

CREATE TABLE newtable LIKE oldtable; 
INSERT newtable SELECT * FROM oldtable;

To copy just structure and data use this one:

CREATE TABLE tbl_new AS SELECT * FROM tbl_old;

Source

Search whole word¶

Source

Export the privileges from MySQL¶

Source

Table size¶

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

Source