-- 用户模板数据
CREATE TABLE `z_user_template` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
`user_id` bigint DEFAULT NULL COMMENT '用户ID',
`phone` varchar(15) NOT NULL DEFAULT '' COMMENT '手机号码',
`email` varchar(30) NOT NULL DEFAULT '' COMMENT '邮箱',
`nickname` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '昵称',
`birthday` date DEFAULT NULL COMMENT '生日',
`gender` tinyint DEFAULT NULL COMMENT '性别：1=男；0=女',
`avatar` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '用户头像',
`slogan` varchar(1000) NOT NULL DEFAULT '' COMMENT '个人说明',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户数据模板';
-- 动态模板数据
CREATE TABLE `z_moment_template` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
`user_id` bigint unsigned NOT NULL COMMENT '用户ID',
`content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '内容',
`images` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '图片（多张图片以逗号隔开）',
`videos` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '视频（多张图片以逗号隔开）',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='动态数据模板';
-- 相册模板数据
CREATE TABLE `z_photo_template` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`user_id` int unsigned NOT NULL COMMENT '用户ID',
`images` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '图片地址',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='相册数据模板';
-- 创建存储过程之前需判断该存储过程是否已存在，若存在则删除
DROP PROCEDURE IF EXISTS addUserData;
-- 创建存储过程
CREATE PROCEDURE addUserData()
BEGIN
-- 定义变量
DECLARE s int DEFAULT 0;
DECLARE userAddId int;
DECLARE id1 int;
DECLARE user_id1 int;
DECLARE phone1 varchar(30);
DECLARE email1 varchar(30);
DECLARE nickname1 varchar(30);
DECLARE birthday1 date;
DECLARE gender1 tinyint;
DECLARE avatar1 varchar(128);
DECLARE slogan1 varchar(800);
-- 定义游标，并将sql结果集赋值到游标中
DECLARE report CURSOR FOR SELECT id, user_id, phone, email, nickname, birthday, gender, avatar, slogan FROM z_user_template;
-- 声明当游标遍历完后将标志变量置成某个值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
-- 打开游标
open report;
-- 将游标中的值赋值给变量，注意：变量名不要和返回的列名同名，变量顺序要和sql结果列的顺序一致
fetch report into id1, user_id1, phone1, email1, nickname1, birthday1, gender1, avatar1, slogan1;
-- 当s不等于1，也就是未遍历完时，会一直循环
while s<>1 do
-- 插入用户数据
INSERT INTO `z_user`(`username`, `password`, `phone`, `email`, `email_status`, `user_id`) VALUES (email1, MD5(email1), phone1, email1, 1, user_id1);
-- 插入UserInfo
-- 获取用户ID
SELECT id into userAddId FROM z_user WHERE user_id = user_id1 ORDER BY id DESC LIMIT 1;
INSERT INTO `z_user_info`(`user_id`, `education`, `slogan`) VALUES (userAddId, NULL, slogan1);
-- 当s等于1时表明遍历以完成，退出循环
fetch report into id1, user_id1, phone1, email1, nickname1, birthday1, gender1, avatar1, slogan1;
end while;
-- 关闭游标
close report;
END;
-- 调用
call addUserData();
-- 动态数据存储过程
-- 创建存储过程之前需判断该存储过程是否已存在，若存在则删除
DROP PROCEDURE IF EXISTS addMomentData;
-- 创建存储过程
CREATE PROCEDURE addMomentData()
BEGIN
-- 定义变量
DECLARE s int DEFAULT 0;
DECLARE userAddId int;
DECLARE user_id1 int;
DECLARE content1 varchar(500);
DECLARE images1 varchar(300);
-- 定义游标，并将sql结果集赋值到游标中
DECLARE report CURSOR FOR SELECT user_id, content, images FROM z_moment_template;
-- 声明当游标遍历完后将标志变量置成某个值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
-- 打开游标
open report;
-- 将游标中的值赋值给变量，注意：变量名不要和返回的列名同名，变量顺序要和sql结果列的顺序一致
fetch report into user_id1, content1, images1;
-- 当s不等于1，也就是未遍历完时，会一直循环
while s<>1 do
-- 获取用户ID
SELECT id into userAddId FROM z_user WHERE user_id = user_id1 ORDER BY id DESC LIMIT 1;
-- 插入数据
INSERT INTO `z_moment`(`user_id`, `content`, `images`) VALUES (userAddId, content1, images1);
-- 当s等于1时表明遍历以完成，退出循环
fetch report into user_id1, content1, images1;
end while;
-- 关闭游标
close report;
END;
-- 调用
call addMomentData();
-- 相册数据存储过程
-- 创建存储过程之前需判断该存储过程是否已存在，若存在则删除
DROP PROCEDURE IF EXISTS addPhotoData;
-- 创建存储过程
CREATE PROCEDURE addPhotoData()
BEGIN
-- 定义变量
DECLARE s int DEFAULT 0;
DECLARE userAddId int;
DECLARE user_id1 int;
DECLARE images1 TEXT;
DECLARE images2 TEXT;
DECLARE sep varchar(3) DEFAULT ',';
DECLARE pos int DEFAULT 1;
-- 定义游标，并将sql结果集赋值到游标中
DECLARE report CURSOR FOR SELECT user_id, images FROM z_photo_template;
-- 声明当游标遍历完后将标志变量置成某个值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
-- 打开游标
open report;
-- 将游标中的值赋值给变量，注意：变量名不要和返回的列名同名，变量顺序要和sql结果列的顺序一致
fetch report into user_id1, images1;
-- 当s不等于1，也就是未遍历完时，会一直循环
while s<>1 do
-- 获取用户ID
SELECT id into userAddId FROM z_user WHERE user_id = user_id1 ORDER BY id DESC LIMIT 1;
while pos > 0 do
-- 查找下标
SET pos = INSTR(images1,sep);
if pos > 0 || length(images1) > 0 THEN
SET images2 = substr(images1, 1, pos - 1); -- 寻找的图片
SET images1 = substr(images1, pos + 1); -- 剩余图片
if pos = 0 && length(images2) = 0 && length(images1) > 0 THEN
SET images2 =images1;
END IF;
if length(images2) > 0 THEN
-- 插入数据
INSERT INTO `z_photo`(`type`, `user_id`, `images`) VALUES (0, userAddId, images2);
END IF;
END IF;
end while;
-- 当s等于1时表明遍历以完成，退出循环
fetch report into user_id1, images1;
end while;
-- 关闭游标
close report;
END;
-- 调用
call addPhotoData();
0 条评论
撰写评论