MYSQL脚本文件--注解、UUID、拼接函数

仅以此篇记录一下对SQL脚本的系列操作

1、原SQL脚本：

/*
Navicat MySQL Data Transfer

Source Server         : 127.0.0.1
Source Server Version : 50620
Source Host           : localhost:3306
Source Database       : mysql_bd

Target Server Type    : MYSQL
Target Server Version : 50620
File Encoding         : 65001

Date: 2022-03-12 10:50:52
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for class
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `stu_id` int(11) DEFAULT NULL,
  `key` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES ('4', '英语', '5', '6e5f936c-a1af-11ec-b36a-507b9d5a29fa');

-- ----------------------------
-- Table structure for stu
-- ----------------------------
DROP TABLE IF EXISTS `stu`;
CREATE TABLE `stu` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `sex` int(1) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  `key` varchar(64) DEFAULT NULL COMMENT '每个区域有唯一key值',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of stu
-- ----------------------------
INSERT INTO `stu` VALUES ('4', '小明', '1', '89', '45eafc75-a1af-11ec-b36a-507b9d5a29fa');
INSERT INTO `stu` VALUES ('5', '小黄', '1', '99', '557ce7d6-a1af-11ec-b36a-507b9d5a29fa');

2、运用注解、UUID()、concat()等操作后的SQL脚本

/*
Navicat MySQL Data Transfer

Source Server         : 127.0.0.1
Source Server Version : 50620
Source Host           : localhost:3306
Source Database       : mysql_bd

Target Server Type    : MYSQL
Target Server Version : 50620
File Encoding         : 65001

Date: 2022-03-12 10:50:52
*/

SET FOREIGN_KEY_CHECKS=0;

-- key值,赋予固定值
SET @key1 = "6e5f936c-a1af-11ec-b36a-507b9d5a29fa";
-- key值,利用UUID()函数生成不唯一值
SET @key2 = UUID();


-- ----------------------------
-- Table structure for class
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `stu_id` int(11) DEFAULT NULL,
  `key` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of class
-- ----------------------------
-- class的key值赋予固定值6e5f936c-a1af-11ec-b36a-507b9d5a29fa
INSERT INTO `class` VALUES ('4', '英语', '5', @key1);

-- ----------------------------
-- Table structure for stu
-- ----------------------------
DROP TABLE IF EXISTS `stu`;
CREATE TABLE `stu` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `sex` int(1) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  `key` varchar(255) DEFAULT NULL COMMENT '每个区域有唯一key值',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of stu
-- ----------------------------
-- stu的key值赋予UUID()生成的不固定值
INSERT INTO `stu` VALUES ('4', '小明', '1', '89', @key2);
-- stu的key值有key1、key2,-,/,&等字符串拼接而成
INSERT INTO `stu` VALUES ('5', '小黄', '1', '99',concat(@key1,@key2,"-","/","&") );