7.Mysql 快速建表，你学会了吗？

有请小发菜

已于 2023-11-14 17:49:31 修改

阅读量144

点赞数 1

分类专栏： Mysql 文章标签： mysql 数据库

于 2023-11-10 13:42:37 首次发布

本文链接：https://blog.csdn.net/weixin_50357986/article/details/134331179

版权

Mysql 专栏收录该内容

28 篇文章 3 订阅

订阅专栏

文章目录

表结构设计，不同的人设计不同，但是尽量规范，于人方便就是于已方便；本文主要介绍如何快速建表。

准备工作

创建一个用于存储表结构设计的表，名建表信息表，建表语法准备如下：

create database asset;
use asset;
drop table if exists table_desc_info;
create table if not exists table_desc_info
(
    id              int auto_increment primary key comment '自增id',
    db_name         varchar(255) default ''                not null comment '数据库名',
    tb_name         varchar(255) default ''                not null comment '表名',
    tb_desc         varchar(255) default '' comment '表描述',
    col_name        varchar(255) default ''                not null comment '字段名',
    col_desc        varchar(255) default '' comment '字段描述',
    col_type        varchar(255) default ''                not null comment '字段类型',
    def_value       varchar(255) default '' comment '默认值，需要写成 default 默认值',
    extra_condition varchar(255) default '' comment '额外条件',
    create_time     datetime     default current_timestamp not null comment '创建时间',
    update_time     datetime     default current_timestamp not null on update current_timestamp comment '修改时间'
) comment '建表信息表';

数据测试

查看我们新建的表结构信息，并且录入到table_desc_info表中；

-- 查看结构信息
desc asset.table_desc_info;
-- 数据录入
insert into table_desc_info(db_name, tb_name, col_name, col_desc, col_type, def_value, extra_condition)
select table_schema               as db_name,
       table_name                 as tb_name,
			 '建表信息表'                 as tb_desc,
       column_name                as col_name,
       column_comment             as col_desc,
       column_type                as col_type,
       ifnull(column_default, '') as def_value,
       case
           when IS_NULLABLE = 'NO' and column_key = 'PRI' then 'auto_increment primary key'
           when IS_NULLABLE = 'NO' and column_key != 'PRI' then 'not null'
           else '' end               extra_condition
from information_schema.columns
where concat(table_schema, '.', table_name) = 'asset.table_desc_info'
order by ordinal_position;

稍微调整下信息，得到如下结果：

select db_name,tb_name,tb_desc,col_name,
       col_desc,col_type,def_value,extra_condition
from asset.table_desc_info;
+---------+-----------------+-----------------+----------------------+--------------------------------------------+--------------+---------------------------+--------------------------------------+
| db_name | tb_name         | tb_desc         | col_name             | col_desc                                   | col_type     | def_value                 | extra_condition                      |
+---------+-----------------+-----------------+----------------------+--------------------------------------------+--------------+---------------------------+--------------------------------------+
| asset   | table_desc_info | 建表信息表      | id                   | 自增id                                     | int          |                           | auto_increment primary key           |
| asset   | table_desc_info | 建表信息表      | db_name              | 数据库名                                   | varchar(255) |                           | not null                             |
| asset   | table_desc_info | 建表信息表      | tb_name              | 表名                                       | varchar(255) |                           | not null                             |
| asset   | table_desc_info | 建表信息表      | col_name             | 字段名                                     | varchar(255) |                           | not null                             |
| asset   | table_desc_info | 建表信息表      | col_desc             | 字段描述                                   | varchar(255) |                           |                                      |
| asset   | table_desc_info | 建表信息表      | col_type             | 字段类型                                   | varchar(255) |                           | not null                             |
| asset   | table_desc_info | 建表信息表      | def_value            | 默认值，需要写成 default 默认值            | varchar(255) |                           |                                      |
| asset   | table_desc_info | 建表信息表      | constraint_condition | 约束条件                                   | varchar(255) |                           |                                      |
| asset   | table_desc_info | 建表信息表      | create_time          | 创建时间                                   | datetime     | default current_timestamp | not null                             |
| asset   | table_desc_info | 建表信息表      | update_time          | 修改时间                                   | datetime     | default current_timestamp | not null on update current_timestamp |
+---------+-----------------+-----------------+----------------------+--------------------------------------------+--------------+---------------------------+--------------------------------------+

根据建表语句，我们只要构造拼接成正常的创建表语句就可以了，经过测试，得到如下代码和结果：

select concat('create table if not exists ','`',db_name,'`','.','`',tb_name,'`','(',char(10)
              ,substring(group_concat(a separator '\n'),2),char(10),') comment ','\'',tb_desc,'\';') ct_statement
from
(select db_name,
       tb_name,
			 tb_desc,
       col_name,
       col_desc,
       col_type,
       def_value,
       extra_condition,
       concat( ',','`',col_name,'`',' ',col_type,' ',def_value,' ',extra_condition,' comment ','\'',col_desc,'\'') a
from asset.table_desc_info) t1
where concat(db_name,'.',tb_name) = 'asset.table_desc_info'
group by db_name,tb_name,tb_desc;
+------------------------------------------------------------------------------------------------------+
| create table if not exists `asset`.`table_desc_info`(
`id` int  auto_increment primary key comment '自增id'
,`db_name` varchar(255)  not null comment '数据库名'
,`tb_name` varchar(255)  not null comment '表名'
,`col_name` varchar(255)  not null comment '字段名'
,`col_desc` varchar(255)   comment '字段描述'
,`col_type` varchar(255)  not null comment '字段类型'
,`def_value` varchar(255)   comment '默认值，需要写成 default 默认值'
,`constraint_condition` varchar(255)   comment '约束条件'
,`create_time` datetime default current_timestamp not null comment '创建时间'
,`update_time` datetime default current_timestamp not null on update current_timestamp comment '修改时间'
) comment '建表信息表';
+-------------------------------------------------------------------------------------------------------+

都是基本的查询语句，cha(10)代表换行符，通过测试调整，最终结果与建表时一致，那么可以投入使用啦。

投入使用

将设计的表结构信息录入到asset.table_desc_info表中，也可以在Datagrip工具中直接像Excel上操作一样，填写好数据提交即可；

insert into asset.table_desc_info (db_name, tb_name, tb_desc, col_name, col_desc, col_type) values ('sql_auto', 'student_info', '学生信息表', 'student_id', '学生id', 'varchar(255)');
insert into asset.table_desc_info (db_name, tb_name, tb_desc, col_name, col_desc, col_type) values ('sql_auto', 'student_info', '学生信息表', 'student_name', '学生姓名', 'varchar(255)');
insert into asset.table_desc_info (db_name, tb_name, tb_desc, col_name, col_desc, col_type) values ('sql_auto', 'student_info', '学生信息表', 'age', '年龄', 'int');

在这里插入图片描述

然后我们试试刚测试的代码，这里通过变量存储下建表语句，再使用execute执行。

create database if not exists sql_auto;
set @ct_statement := (select concat('create table if not exists ','`',db_name,'`','.','`',tb_name,'`','(',char(10)
              ,substring(group_concat(a separator '\n'),2),char(10),') comment ','\'',tb_desc,'\';') ct_statement
from
(select db_name,
       tb_name,
			 tb_desc,
       col_name,
       col_desc,
       col_type,
       def_value,
       extra_condition,
       concat( ',','`',col_name,'`',' ',col_type,' ',def_value,' ',extra_condition,' comment ','\'',col_desc,'\'') a
from asset.table_desc_info) t1
where concat(db_name,'.',tb_name) = 'sql_auto.student_info'
group by db_name,tb_name,tb_desc);
select @ct_statement;-- 查看变量语句情况
prepare stmt1 from @ct_statement;-- 准备语句
execute stmt1;-- 执行语句

-- 查看表是否创建和建表语句
show create table sql_auto.student_info;
+-----------------------------------------------------------------------------------------------+
| student_info | CREATE TABLE `student_info` (
  `student_id` varchar(255) DEFAULT NULL COMMENT '学生id',
  `student_name` varchar(255) DEFAULT NULL COMMENT '学生姓名',
  `age` int DEFAULT NULL COMMENT '年龄'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='学生信息表'              |
+-----------------------------------------------------------------------------------------------+