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';