MySQL quickly obtains the table instance code without primary key in the library

Overview

Let's summarize some SQL statements for viewing tables without primary keys in MySQL database. Let's take a look.

1. View table primary key information

--View table primary key information SELECT
 t.TABLE_NAME,
 t.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 = '<TABLE_NAME>' 
 AND t.TABLE_SCHEMA = '<TABLE_SCHEMA>'; 

2. View the table without primary key

--View the table without primary key SELECT table_schema, table_name,TABLE_ROWS
FROM information_schema.tables
WHERE (table_schema, table_name) NOT IN (
SELECT DISTINCT table_schema, table_name
FROM information_schema.columns
WHERE COLUMN_KEY = 'PRI'
)
AND table_schema NOT IN ('sys', 'mysql', 'information_schema', 'performance_schema'); 

3. Table without primary key

In the Innodb storage engine, each table has a primary key, and the data is organized and stored in the order of the primary key. This type of table is called an index-organized table.

If a primary key is not explicitly defined when a table is defined, a primary key is selected or created as follows:

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

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

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

2) If there is no "non-empty 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, you can use _rowid to display the primary key. The experimental test is as follows:

--Delete the test table DROP TABLE IF EXISTS t1;
​
--Create a test table 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;
​
--Insert 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 _rowid
SELECT *, _rowid FROM t1; 

You can see that the _rowid above is the same as the value of id because the id column is the first unique and NOT NULL index in the table.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.