Consultas interesantes a MySQL

By: Javier on 8/10/2012

Muchas veces MySQL te puede sorprender con la información que almacena y que en muchos casos no sabes de que dispones. A continuación os dejo algunas de esas consultas que pueden resultar de interés para alguno, espero.

Listado de tablas con más de N número de filas

mysql> SELECT table_name as db, table_rows
  -> FROM information_schema.tables 
  -> WHERE table_rows > 1000
  -> AND table_schema not in('information_schema','performance_schema')
  -> ORDER BY table_rows desc;
+---------------------------+------------+
| db                        | table_rows |
+---------------------------+------------+
| zabbix.history            |  127073544 |
| zabbix.history_uint       |   58873482 |
| zabbix.trends_uint        |    1619018 |
| zabbix.trends             |    1310026 |
| zabbix.history_str        |     340954 |
| zabbix.housekeeper        |      50843 |
| zabbix.alerts             |      37877 |
| zabbix.events             |      32635 |
| zabbix.history_log        |      25757 |
| zabbix.items_applications |       1496 |
| zabbix.items              |       1014 |
+---------------------------+------------+
11 rows in set (1.67 sec)

Listado de accesos a una base de datos

mysql> SELECT grantee, privilege_type, is_grantable
  -> FROM information_schema.schema_privileges
  -> WHERE table_schema = 'zabbix';
+----------------------+----------------+--------------+
| grantee              | privilege_type | is_grantable |
+----------------------+----------------+--------------+
| 'zabbix'@'localhost' | SELECT         | NO           |
| 'zabbix'@'localhost' | INSERT         | NO           |
| 'zabbix'@'localhost' | UPDATE         | NO           |
| 'zabbix'@'localhost' | DELETE         | NO           |
| 'zabbix'@'localhost' | CREATE         | NO           |
| 'zabbix'@'localhost' | DROP           | NO           |
| 'zabbix'@'localhost' | REFERENCES     | NO           |
| 'zabbix'@'localhost' | INDEX          | NO           |
| 'zabbix'@'localhost' | ALTER          | NO           |
| 'zabbix'@'localhost' | LOCK TABLES    | NO           |
| 'zabbix'@'localhost' | EXECUTE        | NO           |
| 'zabbix'@'localhost' | CREATE VIEW    | NO           |
| 'zabbix'@'localhost' | SHOW VIEW      | NO           |
| 'zabbix'@'localhost' | CREATE ROUTINE | NO           |
| 'zabbix'@'localhost' | ALTER ROUTINE  | NO           |
| 'zabbix'@'localhost' | EVENT          | NO           |
| 'zabbix'@'localhost' | TRIGGER        | NO           |
+----------------------+----------------+--------------+
18 rows in set (0.00 sec)

Listado de base de datos + tabla que no tienen primary key

mysql> SELECT CONCAT(t.table_schema,'.',t.table_name) as table_name
  -> FROM information_schema.TABLES t
  -> LEFT JOIN information_schema.TABLE_CONSTRAINTS tc
  -> ON t.table_schema = tc.table_schema
  -> AND t.table_name = tc.table_name
  -> AND tc.constraint_type = 'PRIMARY KEY'
  -> WHERE tc.constraint_name IS NULL
  -> AND t.table_type = 'BASE TABLE'
  -> AND t.table_schema not in('information_schema','performance_schema');
+---------------------+
| table_name          |
+---------------------+
| zabbix.events       |
| zabbix.history      |
| zabbix.history_str  |
| zabbix.history_uint |
| zabbix.node_cksum   |
+---------------------+
5 rows in set (1.66 sec)