SQL：查询Mysql表结构

背景：有时需要做数据字典，其中最重要的就是表结构。经整理，编写SQL如下：

 

代码：

 1 -- drop TABLE `cfg_data_dict` ;
 2 CREATE TABLE `cfg_data_dict` (
 3   `id` int NOT NULL AUTO_INCREMENT,
 4   `col_index` int unsigned DEFAULT NULL,
 5   `col_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
 6   `col_type` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
 7   `col_desc` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
 8   `col_prop` varchar(3) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
 9   `table_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
10   `db_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
11   PRIMARY KEY (`id`),
12   KEY `idx_1` (`col_name`,`table_name`,`db_name`)
13 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
14 
15 
16 
17 delete from cfg_data_dict where table_name = 'app_order';
18 
19 insert into cfg_data_dict(col_index,col_name,col_type,col_desc,col_prop,table_name,db_name)
20 select  
21 ORDINAL_POSITION col_index,
22 concat(column_name) col_name,
23 COLUMN_TYPE col_type,
24 column_comment col_desc,
25 concat(
26 (case when IS_NULLABLE='NO' then 'N' else '' end),
27 (case when COLUMN_KEY='PRI' then 'K' else '' end),
28 (case extra  when 'auto_increment' then 'A' else '*'end)
29 ) col_prop,
30 table_name,
31 table_schema db_name
32 from information_schema.columns 
33 where 1=1
34 and table_schema ='app_goods'  and table_name = 'app_order'  
35 order by col_index
36 ;

 

posted @ 2021-11-24 15:15  xiaoyongdata  阅读(224)  评论(0)  编辑  收藏  举报