Useful queries

The post will contain some queries that may provide some useful actions or information on the database. Get the size of all databases:

SELECT table_schema "database", sum(data_length + index_length)/1024/1024 "size in MB" FROM information_schema.TABLES GROUP BY table_schema;

Search for tables with specific names in a given database:

SELECT table_name FROM information_schema.tables where table_schema='<your_database_name>' AND table_name LIKE '%_example';

Copy a table. Please keep in mind that the table may be locked during such query so make sure that no I/O is expected:

CREATE TABLE your_new_db_name.your_new_table_name SELECT * FROM your_db_name.your_old_table_name;

Dump only the structure of a database:

mysqldump -d -h localhost -u root -pmypassword databasename > dumpfile.sql;

Get all column names that contain a given string:

SELECT DISTINCT TABLE_NAME, COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME LIKE '%<search_term>%'
        AND TABLE_SCHEMA='<your_database_name>';

Search table by name:

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%token%'

Calculate percent of all records:

SELECT 
	COUNT(*) AS Total, 
	(SELECT COUNT(*) FROM table_name WHERE column_name = 1) AS `Column Name Total`, 
	(((SELECT COUNT(*) FROM table_name WHERE column_name = 1) * 100) / COUNT(*)) AS `Percent`
FROM 
	table_name

Get value from JSON column:

SELECT invoice,  JSON_UNQUOTE(JSON_EXTRACT(invoice, "$.userName")) FROM purchases p LIMIT 10

Select values that are missing from the database

SELECT missing_values.value
FROM (
    SELECT 'Potentially missing value 1' AS value UNION ALL
    SELECT 'Potentially missing value 2' UNION ALL
    SELECT 'Potentially missing value 3'
    -- Add more values here if needed
) AS missing_values
LEFT JOIN existing_values ON missing_values.value = value_id
WHERE value_id IS NULL;