Mysql存储过程

zs_lwj

已于 2023-09-07 18:00:26 修改

阅读量50

点赞数

文章标签： mysql

于 2023-09-07 17:50:22 首次发布

本文链接：https://blog.csdn.net/qq_28552885/article/details/132743119

版权

一：批量创建表

CREATE DEFINER=`test`@`%` PROCEDURE `test4`()
BEGIN
DECLARE i INT;
DECLARE table_name VARCHAR(32);
SET i = 0;

WHILE i<5 DO
SET table_name = CONCAT('nifi_test_1-table_',i);

SET @csql = CONCAT(
'CREATE TABLE ',table_name,'(
`int_test` int(0) NOT NULL,
`tinyint_Test` tinyint(0) NULL DEFAULT NULL,
`smallint_test` smallint(0) NULL DEFAULT NULL,
`mediumint_test` mediumint(0) NULL DEFAULT NULL,
`bigint_test` bigint(0) NULL DEFAULT NULL,
`float_test` float NULL DEFAULT NULL,
`double_test` double NULL DEFAULT NULL,
`decimal_test` decimal(10, 2) NULL DEFAULT NULL,
`char_test` char(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`varchar_test` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`date_test` date NULL DEFAULT NULL,
`time_test` time(0) NULL DEFAULT NULL,
`datetime_test` datetime(0) NULL DEFAULT NULL,
`year_test` year NULL DEFAULT NULL,
`timestamp_test` timestamp(0) NULL DEFAULT NULL,
`tinytext_test` tinytext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
`text_test` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
`mediumtext_test` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
`longtext_test` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
`longtext_test1` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
`longtext_test2` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
`longtext_test3` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
`longtext_test4` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
`longtext_test5` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
`longtext_test6` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
`longtext_test7` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
`longtext_test8` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
`longtext_test9` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
`longtext_testa` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
`longtext_testb` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
`longtext_testc` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
`longtext_testd` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
`longtext_teste` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
`longtext_testf` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
`binary_test` binary(1) NULL DEFAULT NULL,
`tinyblob_test` tinyblob NULL,
`blob_test` blob NULL,
`mediumblob_test` mediumblob NULL,
`longblob_test` longblob NULL,
`bool_test` tinyint(1) NULL DEFAULT NULL,
`boolean_test` tinyint(1) NULL DEFAULT NULL,
PRIMARY KEY (`int_test`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;'
);

PREPARE create_stmt FROM @csql;
EXECUTE create_stmt;
SET i = i+1;
END WHILE;
END

二：批量插入数据

CREATE DEFINER=`test`@`%` PROCEDURE `test1`()
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE pid INT;
DECLARE p_char CHAR (25);
DECLARE p_longtext LONGTEXT;
WHILE i < 2 DO
SET pid = i;
SET p_char = CONCAT('test_', i);
set p_longtext='在上面的示例代码中，我们创建了一个名为MyProvider的自定义数据提供器，用于生成随机成绩。然后，将自定义数据提供器添加到Faker中，即可使用相应的方法生成自定义数据类型。';
INSERT INTO `kundb_press`(int_test, tinyint_Test, smallint_test, mediumint_test, bigint_test, float_test, double_test, decimal_test, char_test, varchar_test,date_test, time_test, datetime_test, year_test, timestamp_test, tinytext_test, text_test, mediumtext_test, longtext_test, longtext_test1, longtext_test2, longtext_test3, longtext_test4, longtext_test5, longtext_test6, longtext_test7, longtext_test8, longtext_test9, longtext_testa, longtext_testb, longtext_testc, longtext_testd, longtext_teste, longtext_testf, binary_test, tinyblob_test, blob_test, mediumblob_test, longblob_test, bool_test, boolean_test)VALUES(pid, 2, 1985, 1996, 1999, 1989.0, 1986.0, 2001.00, p_char, '在创建表时设置主键在MySQL中,可以在创建表的时候设置主键,语法如下: CREATE TABLE 表名( 列1 数据类型 PRIMARY KEY, 列2 数据类型, 列3 数据类型, ... ); 其中,列1为主键列,它的数据类型可以是数字、字符串等。如果主键由多个列组成,可以在PRIMARY KEY关键字后面加上这些列的名称', NOW(), NOW(), NOW(), NOW(), NOW(),'https://www.baidu.com/s?ie=utf-8&f=8&rsv_bp=1&tn=baidu&wd=mysql%E8%AE%BE%E7%BD%AE%E4%B8%BB%E9%94%AE&oq=mysql', 'https://www.youdao.com/result?word=%22Data%20too%20long%20for%20column%20%27binary_test%27%20at%20row%201%22&lang=en', p_longtext, p_longtext, p_longtext, p_longtext, p_longtext, p_longtext, p_longtext, p_longtext, p_longtext, p_longtext, p_longtext, p_longtext, p_longtext, p_longtext, p_longtext, p_longtext, p_longtext, 0x31, 0x313230363537313330393235323338383731323732383436303033363230373939353731343235393638313331323733303532333138353937352E30, 0x3932363234343431373435363439383135393932392E30, 0x3938313336383139383731303533313636353937332E30, 0x3134343130343135303031333138373131, 1, 1);
SET i = i + 1;
END
WHILE;

END

三：普通创建表

CREATE TABLE `kundb_press` (
`int_test` int(0) NOT NULL,
`tinyint_Test` tinyint(0) NULL DEFAULT NULL,
`smallint_test` smallint(0) NULL DEFAULT NULL,
`mediumint_test` mediumint(0) NULL DEFAULT NULL,
`bigint_test` bigint(0) NULL DEFAULT NULL,
`float_test` float NULL DEFAULT NULL,
`double_test` double NULL DEFAULT NULL,
`decimal_test` decimal(10, 2) NULL DEFAULT NULL,
`char_test` char(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`varchar_test` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`date_test` date NULL DEFAULT NULL,
`time_test` time(0) NULL DEFAULT NULL,
`datetime_test` datetime(0) NULL DEFAULT NULL,
`year_test` year NULL DEFAULT NULL,
`timestamp_test` timestamp(0) NULL DEFAULT NULL,
`tinytext_test` tinytext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
`text_test` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
`mediumtext_test` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
`longtext_test` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
`longtext_test1` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
`longtext_test2` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
`longtext_test3` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
`longtext_test4` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
`longtext_test5` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
`longtext_test6` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
`longtext_test7` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
`longtext_test8` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
`longtext_test9` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
`longtext_testa` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
`longtext_testb` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
`longtext_testc` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
`longtext_testd` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
`longtext_teste` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
`longtext_testf` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
`binary_test` binary(1) NULL DEFAULT NULL,
`tinyblob_test` tinyblob NULL,
`blob_test` blob NULL,
`mediumblob_test` mediumblob NULL,
`longblob_test` longblob NULL,
`bool_test` tinyint(1) NULL DEFAULT NULL,
`boolean_test` tinyint(1) NULL DEFAULT NULL,
PRIMARY KEY (`int_test`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

zs_lwj

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql存储过程

其中,列1为主键列,它的数据类型可以是数字、字符串等。如果主键由多个列组成,可以在PRIMARY KEY关键字后面加上这些列的名称', NOW(), NOW(), NOW(), NOW(), NOW(),'https://www.baidu.com/s?
复制链接

扫一扫