找出没有主键的表

• 2019 年 10 月 4 日
• 筆記

MySQL中， 如果表没有主键的时候，会造成主从延迟。 因此我们需要找出没有主键的表，然后人工加个主键。

脚本如下：

#!/bin/bash  # 找出没有主键的表 (排除MySQL自带的SCHEMA)  source /etc/profile  LOG="/tmp/nopk.log_$(date +%F)"  user='root'  host='localhost'  pass='123456'  sock='/tmp/mysql.sock'  MYSQL_CMD="mysql -u$user -h$host -p$pass -S$sock"  dbs=$($MYSQL_CMD 2>/dev/null -BNe "select SCHEMA_NAME from information_schema.SCHEMATA where SCHEMA_NAME not in ('information_schema','performance_schema','mysql','sys')")  for db in $dbs; do    $MYSQL_CMD information_schema 2>/dev/null -NBe "select distinct TABLE_SCHEMA,table_name from columns where  TABLE_SCHEMA = '$db' and table_name not in ( select distinct table_name from COLUMNS  where TABLE_SCHEMA = '$db' and (column_key  = 'PRI' or column_key = 'UNI') )" | tee -a $LOG  done