Category: MySQL

MySQL query to generate INSERT statements with columns for all tables in the current database: — Increase limit for GROUP_CONCAT() to avoid text cutoff SET SESSION group_concat_max_len = 1048576; SELECT CONCAT( ‘\n\n’, GROUP_CONCAT(CONCAT( ‘INSERT INTO `’, table_name, ‘` (‘, columns, ‘) VALUES ()’ ) SEPARATOR ‘\n\n’), ‘\n’ ) AS result FROM ( SELECT table_name, GROUP_CONCAT(CONCAT(‘`’, …

Continue reading “MySQL query to generate INSERT statements for all tables”

The following query will list the top 5 tables by table size in MySQL. SELECT TABLE_SCHEMA, TABLE_NAME, CEIL((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS table_size_in_MiB, TABLE_ROWS AS estimated_row_count, AVG_ROW_LENGTH FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN (‘information_schema’, ‘innodb’, ‘mysql’, ‘performance_schema’, ‘phpmyadmin’, ‘sys’, ‘tmp’) ORDER BY table_size_in_MiB DESC LIMIT 5; Note that TABLE_ROWS is only accurate …

Continue reading “List tables with table size in MySQL”

Reference: http://stackoverflow.com/questions/3164505/mysql-insert-record-if-not-exists-in-table/#3164741 Supposing we have a database table named distributor and we wish to insert a record only if there is no existing record with the same name and country code. The table only has a PRIMARY key but no UNIQUE index on (name, country_code) – think “legacy” and “no privileges to modify database” 😛 …

Continue reading “Insert record only if it does not exist in table without unique index”