mysql 查询 表空间demo 里第50到第100个表的 表的字段名，字段类型，字段主键 并将该数据存入另一张 table_structure_demo 自定义表中
1建表
CREATE TABLE `table_structure_demo` (
`id` int NOT NULL AUTO_INCREMENT,
`table_name` varchar(255) NOT NULL,
`column_name` varchar(255) NOT NULL,
`data_type` varchar(255) NOT NULL,
`maximum_length` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`is_nullable` varchar(10) DEFAULT NULL,
`is_primary_key` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8192 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
2插入数据
-- INSERT INTO table_structure_demo (table_name, column_name, data_type, character_maximum_length, is_nullable)
WITH top_tables AS (
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'demo'
ORDER BY TABLE_NAME
LIMIT 800 -- OFFSET 49
)
SELECT
t.TABLE_NAME,
c.COLUMN_NAME,
c.DATA_TYPE,
c.CHARACTER_MAXIMUM_LENGTH,
c.IS_NULLABLE
FROM
INFORMATION_SCHEMA.COLUMNS c
JOIN
INFORMATION_SCHEMA.TABLES t ON c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME
JOIN
top_tables tt ON t.TABLE_NAME = tt.TABLE_NAME
WHERE
t.TABLE_SCHEMA = 'demo';
3统计表个数并验证
select * from table_structure_demo group by TABLE_NAME
SELECT COUNT(*) AS table_count
FROM information_schema.tables
WHERE table_schema = 'demo'
AND table_type = 'BASE TABLE';
