背景：有时需要做数据字典，其中最重要的就是表结构。经整理，编写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 ;