mysql动态建表

╰半橙微兮° 2022-07-14 15:30 217阅读 0赞

CREATE PROCEDURE p\_article\_code (IN ss CHAR(20))  
BEGIN DECLARE table\_name VARCHAR (200);

DECLARE sql\_text VARCHAR (2000);  
SET sql\_text = ”;  
SET table\_name = CONCAT(ss,’$article\_info’);

SET sql\_text = CONCAT( ‘CREATE TABLE ‘, table\_name, ‘(  
id BIGINT NOT NULL AUTO\_INCREMENT COMMENT \\’主键ID（自增）\\’,  
title VARCHAR(100) NULL COMMENT \\’文章标题\\’,  
seo\_keywords VARCHAR(100) NULL COMMENT \\’SEO关键字\\’,  
seo\_description VARCHAR(255) NULL COMMENT \\’SEO描述\\’,  
summary VARCHAR(255) NULL COMMENT \\’摘要\\’,  
url VARCHAR(255) NULL COMMENT \\’文章访问地址\\’,  
cover\_num INT NULL DEFAULT 0 COMMENT \\’封面图数量\\’,  
publish\_time INT(10) NULL COMMENT \\’发布时间（10位时间戳）\\’,  
source\_name VARCHAR(100) NULL COMMENT \\’来源名称\\’,  
source\_url VARCHAR(255) NULL COMMENT \\’来源url地址\\’,  
del\_flag CHAR(1) NULL DEFAULT 0 COMMENT \\’是否删除（0: 否；1: 是）\\’,  
del\_time INT(10) NULL COMMENT \\’删除时间（10位时间戳）\\’,  
status CHAR(1) NULL DEFAULT \\’1\\’ COMMENT \\’审核状态（1: 已通过；2: 审核中；3: 未通过）\\’,  
auditing\_time INT(10) NULL COMMENT \\’审核时间（10位时间戳）\\’,  
PRIMARY KEY (id),  
UNIQUE INDEX id\_UNIQUE (id ASC)  
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = \\’文章表\\”);  
SELECT sql\_text;  
SET @sql\_text = sql\_text;  
PREPARE stmt FROM @sql\_text;  
EXECUTE stmt;  
DEALLOCATE PREPARE stmt;

SET sql\_text = ”;  
SET table\_name = CONCAT(ss,’$article\_detail’);

SET sql\_text = CONCAT( ‘CREATE TABLE ‘, table\_name, ‘(  
id BIGINT NOT NULL COMMENT \\’主键ID（非自增，关联product\_info表主键）\\’,  
content LONGTEXT NULL COMMENT \\’文章内容\\’,  
PRIMARY KEY (id),  
UNIQUE INDEX id\_UNIQUE (id ASC)  
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = \\’文章详情表\\” );  
SELECT sql\_text;  
SET @sql\_text = sql\_text;  
PREPARE stmt FROM @sql\_text;  
EXECUTE stmt;  
DEALLOCATE PREPARE stmt;

SET sql\_text = ”;  
SET table\_name = CONCAT(ss,’$article\_count’);

SET sql\_text = CONCAT( ‘CREATE TABLE ‘, table\_name, ‘(  
id BIGINT NOT NULL COMMENT \\’主键ID（非自增，关联article\_info表主键）\\’,  
views INT NULL DEFAULT 0 COMMENT \\’阅读数\\’,  
likes INT NULL DEFAULT 0 COMMENT \\’点赞数\\’,  
favorites INT NULL DEFAULT 0 COMMENT \\’收藏数\\’,  
comments INT NULL DEFAULT 0 COMMENT \\’评论数\\’,  
latest\_update\_time INT(10) NULL COMMENT \\’最后更新时间（10位时间戳）\\’,  
PRIMARY KEY (id),  
UNIQUE INDEX id\_UNIQUE (id ASC)  
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = \\’文章统计表\\” );  
SELECT sql\_text;  
SET @sql\_text = sql\_text;  
PREPARE stmt FROM @sql\_text;  
EXECUTE stmt;  
DEALLOCATE PREPARE stmt;

SET sql\_text = ”;  
SET table\_name = CONCAT(ss,’$article\_tag’);

SET sql\_text = CONCAT( ‘CREATE TABLE ‘, table\_name, ‘(  
id BIGINT NOT NULL AUTO\_INCREMENT COMMENT \\’主键ID（自增）\\’,  
aid BIGINT NULL COMMENT \\’文章ID - article\_info\\’,  
tag\_type VARCHAR(100) NULL COMMENT \\’Tag类型\\’,  
tag VARCHAR(100) NULL COMMENT \\’Tag值\\’,  
PRIMARY KEY (id),  
UNIQUE INDEX id\_UNIQUE (id ASC)  
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = \\’文章标签表\\” );  
SELECT sql\_text;  
SET @sql\_text = sql\_text;  
PREPARE stmt FROM @sql\_text;  
EXECUTE stmt;  
DEALLOCATE PREPARE stmt;

SET sql\_text = ”;  
SET table\_name = CONCAT(ss,’$article\_likes’);

SET sql\_text = CONCAT( ‘CREATE TABLE ‘, table\_name, ‘(  
id BIGINT NOT NULL AUTO\_INCREMENT COMMENT \\’主键ID（自增）\\’,  
aid BIGINT NULL COMMENT \\’文章ID - article\_info\\’,  
uid BIGINT NULL COMMENT \\’用户ID - user\_base\\’,  
like\_time INT(10) NULL COMMENT \\’点赞时间\\’,  
PRIMARY KEY (id),  
UNIQUE INDEX id\_UNIQUE (id ASC)  
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = \\’文章点赞表\\” );  
SELECT sql\_text;  
SET @sql\_text = sql\_text;  
PREPARE stmt FROM @sql\_text;  
EXECUTE stmt;  
DEALLOCATE PREPARE stmt;

SET sql\_text = ”;  
SET table\_name = CONCAT(ss,’$article\_comment’);

SET sql\_text = CONCAT( ‘CREATE TABLE ‘, table\_name, ‘(  
id BIGINT NOT NULL AUTO\_INCREMENT COMMENT \\’主键ID（自增）\\’,  
aid BIGINT NULL COMMENT \\’文章ID - article\_info\\’,  
uid BIGINT NULL COMMENT \\’用户ID - user\_base\\’,  
content VARCHAR(255) NULL COMMENT \\’评论内容\\’,  
comet\_time INT(10) NULL COMMENT \\’评论时间（10位时间戳）\\’,  
del\_flag CHAR(1) NULL DEFAULT 0 COMMENT \\’是否删除（0: 否；1: 是）\\’,  
del\_time INT(10) NULL COMMENT \\’删除时间（10位时间戳）\\’,  
status CHAR(1) NULL DEFAULT \\’1\\’ COMMENT \\’审核状态（1: 已通过；2: 审核中；3: 未通过）- dict\\’,  
auditing\_time INT(10) NULL COMMENT \\’审核时间（10位时间戳）\\’,  
reply CHAR(1) NULL DEFAULT 0 COMMENT \\’是否是回复（0: 否；1: 是）\\’,  
reposted BIGINT NULL COMMENT \\’被回复人 - user\_base\\’,  
PRIMARY KEY (id),  
UNIQUE INDEX id\_UNIQUE (id ASC)  
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = \\’文章评论表\\” );  
SELECT sql\_text;  
SET @sql\_text = sql\_text;  
PREPARE stmt FROM @sql\_text;  
EXECUTE stmt;  
DEALLOCATE PREPARE stmt;

SET sql\_text = ”;  
SET table\_name = CONCAT(ss,’$article\_comment\_count’);

SET sql\_text = CONCAT( ‘CREATE TABLE ‘, table\_name, ‘(  
id BIGINT NOT NULL COMMENT \\’主键ID（非自增，关联article\_comment表主键）\\’,  
likes INT NULL COMMENT \\’点赞数\\’,  
replies INT NULL COMMENT \\’回复数\\’,  
latest\_update\_time INT(10) NULL COMMENT \\’最后更新时间（10位时间戳）\\’,  
PRIMARY KEY (id),  
UNIQUE INDEX id\_UNIQUE (id ASC)  
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = \\’文章评论统计表\\” );  
SELECT sql\_text;  
SET @sql\_text = sql\_text;  
PREPARE stmt FROM @sql\_text;  
EXECUTE stmt;  
DEALLOCATE PREPARE stmt;

SET sql\_text = ”;  
SET table\_name = CONCAT(ss,’$article\_comment\_likes’);

SET sql\_text = CONCAT( ‘CREATE TABLE ‘, table\_name, ‘(  
id BIGINT NOT NULL AUTO\_INCREMENT COMMENT \\’主键ID（自增）\\’,  
cid BIGINT NULL COMMENT \\’文章评论ID - article\_comment\\’,  
uid BIGINT NULL COMMENT \\’用户ID - user\_base\\’,  
like\_time INT(10) NULL COMMENT \\’点赞时间\\’,  
PRIMARY KEY (id),  
UNIQUE INDEX id\_UNIQUE (id ASC)  
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = \\’文章评论点赞表\\” );  
SELECT sql\_text;  
SET @sql\_text = sql\_text;  
PREPARE stmt FROM @sql\_text;  
EXECUTE stmt;  
DEALLOCATE PREPARE stmt;

SET sql\_text = ”;  
SET table\_name = CONCAT(ss,’$article\_cover’);

SET sql\_text = CONCAT( ‘CREATE TABLE ‘, table\_name, ‘(  
id BIGINT NOT NULL AUTO\_INCREMENT COMMENT \\’主键ID（自增）\\’,  
aid BIGINT NULL COMMENT \\’文章ID - article\_info\\’,  
cover VARCHAR(255) NULL COMMENT \\’封面图地址\\’,  
sort INT NULL DEFAULT 0 COMMENT \\’排序（值越大越靠前）\\’,  
del\_flag CHAR(1) NULL DEFAULT 0 COMMENT \\’是否删除（0: 否；1: 是）\\’,  
del\_time INT(10) NULL COMMENT \\’删除时间（10位时间戳）\\’,  
PRIMARY KEY (id),  
UNIQUE INDEX id\_UNIQUE (id ASC)  
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = \\’文章封面图\\” );  
SELECT sql\_text;  
SET @sql\_text = sql\_text;  
PREPARE stmt FROM @sql\_text;  
EXECUTE stmt;  
DEALLOCATE PREPARE stmt;

END;

--------------------

CREATE PROCEDURE p\_product\_code(IN ss CHAR(20))  
BEGIN DECLARE table\_name VARCHAR (200);

DECLARE sql\_text VARCHAR (2000);  
SET sql\_text = ”;  
SET table\_name = CONCAT(ss,’$product\_info’);

SET sql\_text = CONCAT( ‘CREATE TABLE ‘, table\_name, ‘(  
id BIGINT NOT NULL AUTO\_INCREMENT COMMENT \\’主键ID（自增）\\’,  
name VARCHAR(100) NULL COMMENT \\’产品名称\\’,  
seo\_keywords VARCHAR(100) NULL COMMENT \\’SEO关键字\\’,  
seo\_description VARCHAR(255) NULL COMMENT \\’SEO描述\\’,  
summary VARCHAR(255) NULL COMMENT \\’摘要\\’,  
cover VARCHAR(255) NULL COMMENT \\’封面图地址\\’,  
url VARCHAR(255) NULL COMMENT \\’产品访问地址\\’,  
publish\_time INT(10) NULL COMMENT \\’发布时间（10位时间戳）\\’,  
del\_flag CHAR(1) NULL DEFAULT \\’0\\’ COMMENT \\’是否删除（0: 否；1: 是）\\’,  
del\_time INT(10) NULL COMMENT \\’删除时间（10位时间戳）\\’,  
status CHAR(1) NULL DEFAULT \\’1\\’ COMMENT \\’审核状态（1: 已通过；2: 审核中；3: 未通过）\\’,  
auditing\_time INT(10) NULL COMMENT \\’审核时间（10位时间戳）\\’,  
PRIMARY KEY (id),  
UNIQUE INDEX id\_UNIQUE (id ASC)  
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = \\’产品表\\”);  
SELECT sql\_text;  
SET @sql\_text = sql\_text;  
PREPARE stmt FROM @sql\_text;  
EXECUTE stmt;  
DEALLOCATE PREPARE stmt;

SET sql\_text = ”;  
SET table\_name = CONCAT(ss,’$product\_count’);

SET sql\_text = CONCAT( ‘CREATE TABLE ‘, table\_name, ‘(  
id BIGINT NOT NULL COMMENT \\’主键ID（非自增，关联product\_info表主键）\\’,  
views INT NULL DEFAULT 0 COMMENT \\’阅读数\\’,  
likes INT NULL DEFAULT 0 COMMENT \\’点赞数\\’,  
favorites INT NULL DEFAULT 0 COMMENT \\’收藏数\\’,  
latest\_update\_time INT(10) NULL COMMENT \\’最后更新时间（10位时间戳）\\’,  
PRIMARY KEY (id),  
UNIQUE INDEX id\_UNIQUE (id ASC)  
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = \\’产品统计表\\” );  
SELECT sql\_text;  
SET @sql\_text = sql\_text;  
PREPARE stmt FROM @sql\_text;  
EXECUTE stmt;  
DEALLOCATE PREPARE stmt;

SET sql\_text = ”;  
SET table\_name = CONCAT(ss,’$product\_detail’);

SET sql\_text = CONCAT( ‘CREATE TABLE ‘, table\_name, ‘(  
id BIGINT NOT NULL AUTO\_INCREMENT COMMENT \\’主键ID（自增）\\’,  
pid BIGINT NULL COMMENT \\’产品ID - product\_info\\’,  
item\_code VARCHAR(100) NULL COMMENT \\’产品项目编号\\’,  
item\_value TEXT NULL COMMENT \\’产品项目内容（指出HTML标签）\\’,  
PRIMARY KEY (id),  
UNIQUE INDEX id\_UNIQUE (id ASC)  
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = \\’产品详情表\\” );  
SELECT sql\_text;  
SET @sql\_text = sql\_text;  
PREPARE stmt FROM @sql\_text;  
EXECUTE stmt;  
DEALLOCATE PREPARE stmt;

SET sql\_text = ”;  
SET table\_name = CONCAT(ss,’$product\_likes’);

SET sql\_text = CONCAT( ‘CREATE TABLE ‘, table\_name, ‘(  
id BIGINT NOT NULL AUTO\_INCREMENT COMMENT \\’主键ID（自增）\\’,  
pid BIGINT NULL COMMENT \\’产品ID - product\_info\\’,  
uid BIGINT NULL COMMENT \\’用户ID - user\_base\\’,  
like\_time INT(10) NULL COMMENT \\’点赞时间\\’,  
PRIMARY KEY (id),  
UNIQUE INDEX id\_UNIQUE (id ASC)  
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = \\’产品点赞表\\” );  
SELECT sql\_text;  
SET @sql\_text = sql\_text;  
PREPARE stmt FROM @sql\_text;  
EXECUTE stmt;  
DEALLOCATE PREPARE stmt;

SET sql\_text = ”;  
SET table\_name = CONCAT(ss,’$product\_tag’);

SET sql\_text = CONCAT( ‘CREATE TABLE ‘, table\_name, ‘(  
id BIGINT NOT NULL AUTO\_INCREMENT COMMENT \\’主键ID（自增）\\’,  
pid BIGINT NULL COMMENT \\’产品ID - product\_info\\’,  
tag\_type VARCHAR(100) NULL COMMENT \\’Tag类型\\’,  
tag VARCHAR(100) NULL COMMENT \\’Tag值\\’,  
PRIMARY KEY (id),  
UNIQUE INDEX id\_UNIQUE (id ASC)  
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = \\’产品标签表\\” );  
SELECT sql\_text;  
SET @sql\_text = sql\_text;  
PREPARE stmt FROM @sql\_text;  
EXECUTE stmt;  
DEALLOCATE PREPARE stmt;  
END;