MySQL Notes and Tips

Getting table sizes

Getting the size of one table

In bytes:

SELECT (data_length+index_length) tablesize
FROM information_schema.tables
WHERE table_schema='mydb' and table_name='mytable';

In MB:

SELECT (data_length+index_length)/power(1024,2) tablesize_mb
FROM information_schema.tables
WHERE table_schema='mydb' and table_name='mytable';

Listing the sizes of every table

SELECT (data_length+index_length)/power(1024,2) tablesize_mb, table_name FROM information_schema.tables WHERE table_schema='mydb' order by tablesize_mb;

Printing each column on a separate line

Put \G before the semicolon in any SQL statement. It is easier to inspect data with many columns this way, and the final semicolon can be omitted:

select * from table_name limit 3 \G

Showing the current connection

Use SELECT USER(); for the current user and SELECT DATABASE(); for the current database. To show the current status, use status;, or simply \s; it includes the current user and database as well.

Finding redundant duplicate rows (several fields)

Put simply, these are rows with the same values in several fields. To find them, group by those fields:

SELECT * from table_name
where name=target_name and color=target_color
group by name, color having count(*) > 1;

For example, find records with the same name and color:

The result is:

It returns one row from the records with the same name and color, because the SQL means to find one record for which both fields are the same. There is a small advantage: if there are at most two duplicate rows, deleting one of them is enough to remove the duplicate.

Trying this:

delete from table_name
where name=target_name and color=target_color
group by name, color having count(*)>1;

produces this error in MySQL:

You can’t specify target table ‘user_userboxperm’ for update in FROM clause

MySQL cannot modify table data by referring to the result of an inner query. Put the table inside another subquery instead:

    delete from table_name
    where id in (
        select id from (
            select * from table_name where name=target_name and color=target_color
            group by name, color having count(*) > 1
        )as a
    );

To get every duplicate row:

SELECT * FROM table_name a
WHERE (a.id, a.name) IN
(SELECT id, name FROM table_name GROUP BY id, name HAVING COUNT(*) > 1);

This method is simple but not very efficient. It may not suit tables with more than ten thousand rows. For larger tables, creating a temporary table is a better approach:

CREATE TABLE temp_table as (
    SELECT id, name
    FROM table_name
    GROUP BY id, name HAVING COUNT(*) >1
);

SELECT *　FROM table_name a, temp_table b
WHERE a.id = b.id AND a.name = b.name;

Selecting N random rows

rand() is commonly used, but RAND() should not be used in an ORDER BY clause because it makes the data rows scan repeatedly and becomes slow.

Using a where clause:

SELECT * FROM table_name
WHERE id >= (SELECT floor(RAND() * (SELECT MAX(id) FROM table_name)) )
ORDER BY id LIMIT 1;

Using a join (the N rows are contiguous):

SELECT * FROM table_name AS t1 JOIN
(SELECT ROUND(RAND() * ((SELECT MAX(id) FROM table_name)-(SELECT MIN(id) FROM table_name))
　+(SELECT MIN(id) FROM table_name)) AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id LIMIT 1;

Using a join (the N rows are not contiguous):

SELECT * FROM table_name AS t1 JOIN
(SELECT ROUND(RAND() * ((SELECT MAX(id) FROM table_name)-(SELECT MIN(id) FROM table_name))+　(SELECT MIN(id) FROM table_name)) AS id from table_name limit 50) AS t2
on t1.id=t2.id
ORDER BY t1.id LIMIT 1;

Checking whether a field contains a substring

The simplest way is LIKE:

SELECT * FROM  table_name WHERE name LIKE '%keyword%';

locate and instr can be used instead of LIKE, and are a little faster. locate takes locate(substr, str, pos). locate, position, and instr differ only in parameter order; locate also has a starting-position argument.

SELECT * FROM table_name WHERE locate('keyword', name)>0;
SELECT * FROM table_name WHERE position('keyword' IN name);
SELECT * FROM table_name WHERE instr(name, 'keyword')>0;

Deleting every table with the same prefix

Use group_concat to add every target table to a drop statement. The table information comes from information_schema.tables:

SELECT CONCAT('DROP TABLE ', GROUP_CONCAT(table_name) , ';' )
    AS statement FROM information_schema.tables
    WHERE table_name LIKE 'prefix_%';

Getting the create-table statements for all tables

To get the create-table statements for existing tables in a database:

mysqldump -h localhost -u root -p --no-data --compact DB_NAME

Add TABLE_NAME after DB_NAME (separating tables with spaces) to export particular tables. You can redirect the result into a .sql file with >. To inspect one table, use:

SHOW CREATE TABLE TABLE_NAME;

Importing and exporting

mysqldump -uroot -p -hlocalhost --single-transaction DB_NAME > backup

--single-transaction does not lock the database. To lock it, use --lock-all-tables. You can also compress the backup with | gzip > backup.tar.gz.

Importing from a .sql file

mysql -uroot -p -hlocalhost  -Ddb_name < backup.sql

Originally published on SegmentFault.