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

This article shows you how to get the table without primary key in the MySQL database, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

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 =' 'AND t.TABLE_SCHEMA =''

two。 View no primary key table

View no primary key table

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. 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, and this kind of table becomes the index organization table Index Ogranized Table.

If the definition primary key is not displayed when the table is defined, the primary key is selected or created as follows:

(1) first determine whether there is a "non-empty unique index" in the table, and if so

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

If there are more than one non-empty unique index, select the first defined non-empty unique index as the primary key according to the order of the index.

(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, you can use _ rowid to display the primary key. The experimental test is as follows:

Delete 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 =

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

The above content is how to get the table without primary key in the MySQL database. Have you learned the knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.