Shulou(Shulou.com)05/31 Report--

This article will explain in detail how mysql can quickly obtain tables without primary keys in the library. Xiaobian thinks it is quite practical, so share it with you as a reference. I hope you can gain something after reading this article.

1. View table primary key information

Table_CONSTRAINT_TYPE, c.COLUMN_NAME, c.ORDINAL_POSITION FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t, INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c WHERE t.TABLE_NAME = c.TABLE_NAME AND t.CONSTRAINT_TYPE = 'PRIMARY KEY' AND t.TABLE_NAME = '' AND t.TABLE_SCHEMA = '';

2. View tables without primary keys

SELECT table_schema, table_name,TABLE_ROWSFROM information_schema.tablesWHERE (table_schema, table_name) NOT IN (SELECT DISTINCT table_schema, table_name FROM information_schema.columnsWHERE COLUMN_KEY ='PRI ') AND table_schema NOT IN ('sys', 'mysql', 'information_schema', 'performance_schema');

3. No primary key table

In the Innodb storage engine, each table has a primary key, and the data is organized and stored according to the primary key order. This type of table is called the index organization table Index Ogranulated Table.

If the table definition does not display the definition primary key, the primary key is selected or created as follows:

1)First determine whether there is a "non-empty unique index" in the table, if there is

If there is only one "non-empty unique index," the index is the primary key

If there are multiple "non-empty unique indexes", select the first defined non-empty unique index as the primary key according to the order of index indexes.

2)If there is no "non-null unique index" in the table, a 6-byte pointer is automatically created as the primary key.

If the primary key index has only one index key, then you can use_rowid to display the primary key. The experimental test is as follows:

--DROP TABLE IF EXISTS t1;--CREATE TABLE t1 ( `id` int(11) NOT NULL, `c1` int(11) DEFAULT NULL, UNIQUE uni_id (id), INDEX idx_c1(c1)) ENGINE = InnoDB CHARSET = utf8;--interpolate test data INSERT INTO t1 (id, c1) SELECT 1, 1;INSERT INTO t1 (id, c1) SELECT 2, 2; INSERT INTO t1 (id, c1) SELECT 4, 4;--View data and_rowidSELECT *, _rowid FROM t1;

You can see that the above_rowid has the same value as id, because the id column is the first unique NOT NULL index in the table.

About "mysql how to quickly get no primary key table in the library" this article is shared here, I hope the above content can be of some help to everyone, so that you can learn more knowledge, if you think the article is good, please share it to let more people see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.