MySQL > select * from table where primary key is not used

Preface:

In MySQL, it is common to require primary keys when creating a table. If the requirements are not standardized, there will inevitably be several tables without a primary key. In this article, let’s find out the table without a primary key.

1. The harm of no primary key table

Take InnoDB table as an example, we all know that in InnoDB, tables are stored in the form of index according to the primary key order, this storage way of table is called index organized table. An InnoDB table must have a cluster index, and if it has a primary key, it will have the primary key as the cluster index. If no explicit primary key is defined, InnoDB selects a unique non-empty index instead. If there is no such index, MySQL automatically generates an implied field for the InnoDB table as the primary key.

That is, it would be better if we could explicitly define the primary key, so what harm might a table without a primary key do? First of all, no primary key means that the primary key index cannot be used, which may affect the query efficiency. Second, it is not maintenancy-friendly. For example, if you want to upgrade to a MGR cluster or use some open source tools, the table will be required to have a primary key. Another point is that batch updates or deletions of tables without primary keys can easily cause long master-slave delays.

By the way, when the master database updates or deletes a large number of tables without a primary key (especially those with neither a primary key nor an index), the slave database may experience significant master-slave delays, or even be stuck for a long time. If you want to catch up with a table that has no primary key, you can manually set this table to ignore synchronization. If you want to catch up with a table that has no primary key, you can manually set this table to ignore synchronization. If you want to catch up with a table that has no primary key, you can set this table to ignore synchronization. Process SQL as follows:

Mysql > select * from testtb where mysql> STOP SLAVE SQL_THREAD; mysql> select * from testtb where mysql> STOP SLAVE SQL_THREAD; Query OK, 0 rows affected (0.00 SEC) mysql> CHANGE REPLICATION FILTER REPLICATE_IGNORE_TABLE = (db.testTB); Query OK, 0 rows affected (0.00 SEC) mysql> CHANGE REPLICATION FILTER REPLICATE_IGNORE_TABLE = (db.testTB); Query OK, 0 rows affected (0.00 SEC) mysql> START SLAVE SQL_THREAD; Query OK, 0 rows affected (0.01sec)

Ignoring the synchronization of this table, the slave will soon catch up with the master. You can then add a primary key to the table, and then manually synchronize it and unignore it.

Mysql > find table with no primary key

When we have many, many tables in our database instance, how do we find a table with or without a primary key? You can’t find them one by one. If you are smart, you can find them in MySQL’s system table, because all of our table building information is stored in the system library INFORMATION_SCHEMA. Select * from table where primary key is not present;

# a repository without primary key table lookup (no primary key table will have a unique key was found) SELECT t1. Table_schema, t1.table_name FROM information_schema.TABLES t1 LEFT OUTER JOIN information_schema.TABLE_CONSTRAINTS t2 ON t1.table_schema = t2.TABLE_SCHEMA AND t1.table_name = t2.TABLE_NAME AND t2.CONSTRAINT_NAME IN ('PRIMARY') WHERE t2.table_name IS NULL AND t1.table_type = 'BASE TABLE' AND t1.TABLE_SCHEMA = 'testdb'; SELECT t1.table_schema from t1.table_schema where (SELECT t1.table_schema from t1.table_schema); t1.table_name FROM information_schema.TABLES t1 LEFT OUTER JOIN information_schema.TABLE_CONSTRAINTS t2 ON t1.table_schema = t2.TABLE_SCHEMA AND t1.table_name = t2.TABLE_NAME AND t2.CONSTRAINT_NAME IN ('PRIMARY') WHERE t2.table_name IS NULL AND t1.table_type = 'BASE TABLE' AND t1.TABLE_SCHEMA NOT IN ( 'information_schema', 'performance_schema', 'mysql', 'sys' );

If you find a table without a primary key, the next step is to add a primary key to the table. Whether you use auto-increment id, uuid, or other algorithmically generated primary key fields, it is recommended to add a primary key to the table. For a table with no primary key, we could add a primary key like this:

ALTER TABLE tb1 ADD COLUMN inc_id INT UNSIGNED NOT NULL auto_increment COMMENT PRIMARY KEY ALTER TABLE tb1 ADD COLUMN inc_id INT UNSIGNED NOT NULL auto_increment COMMENT PRIMARY KEY FIRST; SELECT CONCAT('ALTER TABLE ',t1.table_schema,'.',t1.table_name,' ADD COLUMN inc_id INT UNSIGNED ') SELECT CONCAT('ALTER TABLE ',t1.table_schema,' NOT NULL auto_increment COMMENT \' PRIMARY KEY FIRST '; ') FROM information_schema.TABLES t1 LEFT OUTER JOIN information_schema.TABLE_CONSTRAINTS t2 ON t1.table_schema = t2.TABLE_SCHEMA AND t1.table_name = t2.TABLE_NAME AND t2.CONSTRAINT_NAME IN ('PRIMARY') WHERE t2.table_name IS NULL AND t1.table_type = 'BASE TABLE' AND t1.TABLE_SCHEMA NOT IN ( 'information_schema', 'performance_schema', 'mysql', 'sys' ) ;

Conclusion:

This article focuses on the potential hazards of a table without a primary key and how to find out if a table without a primary key exists. Some of the SQL in the paper are based on the system table to find, you can save to their own environment to try oh. MySQL table or mandatory primary key is good, people have to have their own idea, the table must also have a primary key!