mysql查询没有主键的表

 

 

mysql> SELECT t.table_schema, t.table_name FROM information_schema.tables t LEFT JOIN information_schema.columns c ON t.table_schema = c.table_schema AND t.table_name = c.table_name WHERE
t.table_type = 'BASE TABLE' AND t.table_schema NOT IN ('mysql', 'sys', 'performance_schema', 'information_schema') GROUP BY t.table_schema, t.table_name HAVING sum(if(c.column_key='PRI', 1
, 0)) = 0;
+--------------+------------+
| table_schema | table_name |
+--------------+------------+
| emm_imserver | t1 |
| emm_imserver | t2 |
| emm_imserver | t3 |
| test | t |
+--------------+------------+
4 rows in set, 48 warnings (0.37 sec)

mysql>