mysql动态建表
CREATEPROCEDURE 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 =
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;
更多推荐
所有评论(0)