1.oracle
创建表
CREATE TABLE "YZH2_ORACLE" (
"VARCHAR2_COLUMN" VARCHAR2(20) NOT NULL ENABLE,
"NUMBER_COLUMN" NUMBER,
"DATE_COLUMN" DATE,
"CLOB_COLUMN" CLOB,
"BLOB_COLUMN" BLOB,
"BINARY_DOUBLE_COLUMN" BINARY_DOUBLE,
"BINARY_FLOAT_COLUMN" BINARY_FLOAT,
"CHAR_COLUMN" CHAR(1),
"CHAR_VARYING_COLUMN" VARCHAR2(20),
"DEC_COLUMN" NUMBER(*, 0),
"DECIMAL_COLUMN" NUMBER(*, 0),
"DOUBLE_PRECISION_COLUMN" FLOAT(126),
"CHARACTER_COLUMN" CHAR(1),
"CHARACTER_VARYING_COLUMN" VARCHAR2(20),
"FLOAT_COLUMN" FLOAT(126),
"INT_COLUMN" NUMBER(*, 0),
"INTEGER_COLUMN" NUMBER(*, 0),
"NATIONAL_CHAR_COLUMN" NCHAR(1),
"NATIONAL_CHAR_VARYING_COLUMN" NVARCHAR2(20),
"NATIONAL_CHARACTER_COLUMN" NCHAR(1),
"NATIONAL_CHARACTER_VARY_COLUMN" NVARCHAR2(20),
"NCHAR_COLUMN" NCHAR(1),
"NCHAR_VARYING_COLUMN" NVARCHAR2(20),
"NCLOB_COLUMN" NCLOB,
"NUMERIC_COLUMN" NUMBER(*, 0),
"NVARCHAR2_COLUMN" NVARCHAR2(20),
"RAW_COLUMN" RAW(20),
"REAL_COLUMN" FLOAT(63),
"SMALLINT_COLUMN" NUMBER(*, 0),
"TIMESTAMP_COLUMN" TIMESTAMP (6),
"VARCHAR_COLUMN" VARCHAR2(20)
)
预设游标10000（必要时）
alter system set open_cursors = 10000;
模拟新增10万条数据
DECLARE I number := 0;
BEGIN
FOR I IN 0..100000 LOOP
INSERT INTO YZH2_ORACLE (
VARCHAR2_COLUMN, NUMBER_COLUMN, BINARY_DOUBLE_COLUMN,
BINARY_FLOAT_COLUMN, CHAR_VARYING_COLUMN,
DEC_COLUMN, DECIMAL_COLUMN, DOUBLE_PRECISION_COLUMN,
CHARACTER_VARYING_COLUMN, FLOAT_COLUMN,
INT_COLUMN, INTEGER_COLUMN, NATIONAL_CHAR_VARYING_COLUMN,
NATIONAL_CHARACTER_VARY_COLUMN,
NCHAR_VARYING_COLUMN, NUMERIC_COLUMN,
NVARCHAR2_COLUMN, REAL_COLUMN, SMALLINT_COLUMN,
VARCHAR_COLUMN
)
VALUES (I, I, I, I, I, I, I, I, I, I, I, I, I, I, I, I,I, I, I, I);
END LOOP;
COMMIT;
END;
1.1 oracle一条sql数据生成100万条数据
SELECT
rownum AS id,
to_char(sysdate + rownum / 24 / 3600, 'yyyy-mm-dd hh24:mi:ss') AS inc_datetime,
trunc(dbms_random.value(0, 100)) AS random_id,
dbms_random.string('x',20) AS random_string
FROM
dual
CONNECT BY
LEVEL <= 1000000; #可传参数 
2.SQL SERVER
创建测试表
CREATE TABLE dbo.yzh2_ms_checker (
BIGINT_COLUMN bigint NULL DEFAULT (NULL),
BINARY_COLUMN binary(64) NULL DEFAULT (NULL),
BIT_COLUMN bit NULL DEFAULT (NULL),
CHAR_COLUMN char(64) NULL DEFAULT (NULL),
CHAR_MAX_COLUMN char(128) NULL DEFAULT (NULL),
DATE_COLUMN date NULL DEFAULT (NULL),
DATETIME_COLUMN datetime NULL DEFAULT (NULL),
DECIMAL_COLUMN decimal(18,0) NULL DEFAULT (NULL),
FLOAT_COLUMN real NULL DEFAULT (NULL),
INT_COLUMN int NULL DEFAULT (NULL),
MONEY_COLUMN money NULL DEFAULT (NULL),
NCHAR_COLUMN nchar(64) NULL DEFAULT (NULL),
NCHAR_MAX_COLUMN nchar(128) NULL DEFAULT (NULL),
NTEXT_COLUMN ntext NULL DEFAULT (NULL),
NUMERIC_COLUMN numeric(18,0) NULL DEFAULT (NULL),
NVARCHAR_COLUMN nvarchar(64) NULL DEFAULT (NULL),
NVARCHAR_MAX_COLUMN nvarchar(128) NULL DEFAULT (NULL),
REAL_COLUMN real NULL DEFAULT (NULL),
SMALLINT_COLUMN smallint NULL DEFAULT (NULL),
SMALLMONEY_COLUMN smallmoney NULL DEFAULT (NULL),
TEXT_COLUMN text NULL DEFAULT (NULL),
TINYINT_COLUMN tinyint NULL DEFAULT (NULL),
VARBINARY_COLUMN varbinary(64) NULL DEFAULT (NULL),
VARCHAR_COLUMN varchar(64) NULL DEFAULT (NULL),
VARCHAR_MAX_COLUMN varchar(128) NULL DEFAULT (NULL),
XML_COLUMN xml NULL DEFAULT (NULL)
);
GO
DECLARE @i INT
SET
@i = 1
WHILE @i <=30000 BEGIN
INSERT INTO dbo.yzh2_ms_checker (
BIGINT_COLUMN, BINARY_COLUMN, BIT_COLUMN,
CHAR_COLUMN, CHAR_MAX_COLUMN, DATE_COLUMN,
DATETIME_COLUMN, DECIMAL_COLUMN,
FLOAT_COLUMN, INT_COLUMN, MONEY_COLUMN,
NCHAR_COLUMN, NCHAR_MAX_COLUMN,
NTEXT_COLUMN, NUMERIC_COLUMN, NVARCHAR_COLUMN,
NVARCHAR_MAX_COLUMN, REAL_COLUMN,
SMALLINT_COLUMN, SMALLMONEY_COLUMN,
TEXT_COLUMN, TINYINT_COLUMN, VARBINARY_COLUMN,
VARCHAR_COLUMN, VARCHAR_MAX_COLUMN,
XML_COLUMN
)
VALUES
(
@i,1,@i,@i,@i,GETDATE(),GETDATE(),@i,@i,@i,@i,@i,@i,STR(@i),
@i,STR(@i),STR(@i),@i,@i,@i,STR(@i),NULL,1,@i,@i,STR(@i)
)
SET @i = @i + 1
END;
3.MySQL
创建测试表
CREATE TABLE `yzh2_sync` (
`bigint_column` bigint(10) NOT NULL AUTO_INCREMENT COMMENT '1',
`bit_column` bigint(10) NOT NULL COMMENT '2',
`blob_column` blob NOT NULL COMMENT '3',
`char_column` char(64) NOT NULL COMMENT '4',
`date_column` date NOT NULL COMMENT '5',
`datetime_column` datetime NOT NULL COMMENT '6',
`decimal_column` decimal(18,9) NOT NULL COMMENT '7',
`double_column` double NOT NULL COMMENT '8',
`float_column` float NOT NULL COMMENT '10',
`int_column` int(11) NOT NULL COMMENT '11',
`longblob_column` longblob NOT NULL COMMENT '12',
`longtext_column` longtext NOT NULL COMMENT '13',
`mediumblob_column` mediumblob NOT NULL COMMENT '14',
`mediumint_column` mediumint(9) NOT NULL COMMENT '15',
`mediumtext_column` mediumtext NOT NULL COMMENT '16',
`smallint_column` smallint(6) NOT NULL COMMENT '18',
`text_column` text NOT NULL COMMENT '19',
`time_column` time NOT NULL COMMENT '20',
`timestamp_column` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '21',
`tinyint_column` int(11) NOT NULL COMMENT '22',
`tinytext_column` tinytext NOT NULL COMMENT '23',
`varchar_column` varchar(32) NOT NULL COMMENT '24',
PRIMARY KEY (`bigint_column`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
模拟新增3万条数据
DROP PROCEDURE if EXISTS test_insert;
DELIMITER //
CREATE PROCEDURE test_insert()
BEGIN
DECLARE y BIGINT DEFAULT 0;
WHILE y<30000
DO
INSERT INTO yzh2_sync (
bit_column, blob_column, char_column,
date_column, datetime_column, decimal_column,
double_column, float_column, int_column,
longblob_column, longtext_column,
mediumblob_column, mediumint_column,
mediumtext_column, smallint_column,
text_column, time_column, timestamp_column,
tinyint_column, tinytext_column,
varchar_column)
VALUES(
y, y, y,
now(), now(), y,
y, y, y,
y, y,
y, y,
y, y,
y,now(), now(),
y, y,
y
);
SET y=y+1;
END WHILE ;
commit;
END //
{ CALL xag.test_insert() }
开发过程中经常需要测试 SQL 在大量数据集时候的执行效率，这就需要我们在表中插入大量的测试数据，下面介绍如何使用存储过程插入大量的测试数据
4.定义常用方法
我们要确保生成的测试数据要有足够的随机性，测试结果才会更准确，如果某个字段的测试数据都是一样的，索引的效率会大大折扣，测试结果往往与真实数据的执行结果大相径庭
我们可以使用 MySQL 的自定义函数来实现随机值的生成，下面罗列出几种常见的字段的函数定义
生成随机时间
函数声明：
CREATE DEFINER=`root`@`%` FUNCTION `genDate`(
start_time VARCHAR(10),
end_time VARCHAR(10)
) RETURNS VARCHAR(255) CHARSET utf8mb4
BEGIN
DECLARE random_date DATETIME DEFAULT NULL;
SET random_date = CONCAT(
(DATE(FROM_UNIXTIME(UNIX_TIMESTAMP(start_time) + FLOOR(RAND() * (UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time) + 1))))),
' ',
FLOOR(RAND() * 24), ':', FLOOR(RAND() * 60), ':', FLOOR(RAND() * 60)
);
RETURN date_format(random_date,'%Y-%m-%d %H:%i:%s');
END
select genDate('2020-01-01','2023-01-01');
生成中文名
函数声明：
CREATE DEFINER=`root`@`%` FUNCTION `genUsername`() RETURNS varchar(255) CHARSET utf8mb4
BEGIN
DECLARE first_name_dict VARCHAR(2056) DEFAULT '赵钱孙李周郑王冯陈楮卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤滕殷罗毕郝邬安常乐于时傅皮齐康伍余元卜顾孟平黄和穆萧尹姚邵湛汪祁毛禹狄米贝明臧计伏成戴谈宋茅庞熊纪舒屈项祝董梁杜阮蓝闽席季麻强贾路娄危江童颜郭梅盛林刁锺徐丘骆高夏蔡田樊胡凌霍虞万支柯昝管卢莫经裘缪干解应宗丁宣贲邓郁单杭洪包诸左石崔吉钮龚程嵇邢滑裴陆荣翁';
DECLARE last_name_dict VARCHAR(2056) DEFAULT '嘉懿煜城懿轩烨伟苑博伟泽熠彤鸿煊博涛烨霖烨华煜祺智宸正豪昊然明杰诚立轩立辉峻熙弘文熠彤鸿煊烨霖哲瀚鑫鹏致远俊驰雨泽烨磊晟睿天佑文昊修洁黎昕远航旭尧鸿涛伟祺轩越泽浩宇瑾瑜皓轩擎苍擎宇志泽睿渊楷瑞轩弘文哲瀚雨泽鑫磊梦琪忆之桃慕青问兰尔岚元香初夏沛菡傲珊曼文乐菱痴珊恨玉惜文香寒新柔语蓉海安夜蓉涵柏水桃醉蓝春儿语琴从彤傲晴语兰又菱碧彤元霜怜梦紫寒妙彤曼易南莲紫翠雨寒易烟如萱若南寻真晓亦向珊慕灵以蕊寻雁映易雪柳孤岚笑霜海云凝天沛珊寒云冰旋宛儿绿真盼儿晓霜碧凡夏菡曼香若烟半梦雅绿冰蓝灵槐平安书翠翠风香巧代云梦曼幼翠友巧听寒梦柏醉易访旋亦玉凌萱访卉怀亦笑蓝春翠靖柏夜蕾冰夏梦松书雪乐枫念薇靖雁寻春恨山从寒忆香觅波静曼凡旋以亦念露芷蕾千兰新波代真新蕾雁玉冷卉紫山千琴恨天傲芙盼山怀蝶冰兰山柏翠萱乐丹翠柔谷山之瑶冰露尔珍谷雪乐萱涵菡海莲傲蕾青槐冬儿易梦惜雪宛海之柔夏青亦瑶妙菡春竹修杰伟诚建辉晋鹏天磊绍辉泽洋明轩健柏煊昊强伟宸博超君浩子骞明辉鹏涛炎彬鹤轩越彬风华靖琪明诚高格光华国源宇晗昱涵润翰飞翰海昊乾浩博和安弘博鸿朗华奥华灿嘉慕坚秉建明金鑫锦程瑾瑜鹏经赋景同靖琪君昊俊明季同开济凯安康成乐语力勤良哲理群茂彦敏博明达朋义彭泽鹏举濮存溥心璞瑜浦泽奇邃祥荣轩';
DECLARE first_name VARCHAR(3) DEFAULT substring(first_name_dict, floor(length(first_name_dict) / 3 * rand()), 1);
DECLARE last_name VARCHAR(9);
DECLARE full_name_length INT DEFAULT FLOOR(2+(RAND()*3))*3;
DECLARE full_name VARCHAR(12) DEFAULT first_name;
WHILE LENGTH(full_name) < full_name_length DO
SET full_name = CONCAT(full_name, substring(last_name_dict, floor(length(last_name_dict) / 3 * rand()), 1));
END WHILE;
return full_name;
end
select genUsername(); 
字符串分割选取
函数声明：
CREATE FUNCTION `splitStr` (
str VARCHAR (1000),
delimiter VARCHAR (5),
str_order INT
) RETURNS VARCHAR (255) CHARSET utf8mb4 DETERMINISTIC
BEGIN
DECLARE result VARCHAR (255) DEFAULT '';
SET result = REVERSE(
substring_index(
REVERSE(
substring_index(
str,
delimiter,
str_order
)
),
delimiter,
1
)
);
RETURN result;
end
select splitStr('I love MySQL',' ',2);
使用示例：该函数用于将字符串按照指定的分割符进行分割，并返回分割后的第 n（n 由参数指定） 个字符串，如取字符串”I love MySQL“按空格分割后的第 2 个字符串
生成随机手机号
函数声明：
CREATE DEFINER=`root`@`%` FUNCTION `genMobile`() RETURNS char(11) CHARSET utf8mb4 NOT DETERMINISTIC
BEGIN
DECLARE head VARCHAR(100) DEFAULT '132,133,139,183,186,187,130,131,189,151,156,157,176,134,135,137,138,136,000';
DECLARE content CHAR(10) DEFAULT '0123456789';
DECLARE phone CHAR(20) DEFAULT splitStr(head, ',', FLOOR(1 + RAND() * 19));
DECLARE i int DEFAULT 1;
WHILE i<9 DO
SET i=i+1;
SET phone = CONCAT(phone, substring(content, floor(1 + RAND() * 10), 1));
END WHILE;
RETURN phone;
end
select genMobile();
插入大量测试数据
如下面这张表，现在要插入 10w 的测试数据，我们可以定义一个 MySQL 存储过程，通过存储过程的方式插入数据到表中
表结构
CREATE TABLE `t_user` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(50) DEFAULT NULL,
`sex` tinyint(1) DEFAULT NULL,
`mobile` varchar(45) DEFAULT NULL,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`user_id`),
KEY `idx_create_time` (`create_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4存储过程定义
CREATE DEFINER=`root`@`%` PROCEDURE `t_user_batch_insert`(IN size INT)
BEGIN
declare i int default 0;
while i < size do
insert into t_user(username,sex,mobile) values(genUsername(),floor(rand() * 2),genMobile());
set i = i + 1;
end while;
END调用存储过程
> call t_user_batch_insert(100000);
在我这边，插入 10w 条数据，只要 52s
延伸
除了使用存储过程的方法插入数据外，还可以通过代码的方式插入数据，但是该方法的执行效率不高，但是改方法的执行效率不高。另外，如果你有 navicat 的话，也可以试试 navicat 的数据生成方案，由于我没有 navicat，就不介绍了，感兴趣的可以看 navicat 的文档
5.常用的MySQL测试数据批量生成方式
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL COMMENT '用户名',
`role_id` int(11) DEFAULT NULL COMMENT '角色id',
`password` varchar(255) DEFAULT NULL COMMENT '密码',
`salt` varchar(255) DEFAULT NULL COMMENT '盐',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `role` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`role_name` varchar(255) DEFAULT NULL COMMENT '角色名',
`orders` int(11) DEFAULT NULL COMMENT '排序权重\r\n',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
使用函数生成
通过存储过程快速插入, 通过函数保证数据不重复
设置允许创建函数
查看 MySQL是否允许创建函数
SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
结果如图所示, 我们使用以下命令将创建函数功能打开（global-所有session都生效）
SET GLOBAL log_bin_trust_function_creators=1;
这个时候再一次查询就会显示已打开
产生随机字符串
-- 随机产生字符串
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END $$
-- 假如要删除
-- drop function rand_string;
产生随机数字
-- 用于随机产生区间数字
DELIMITER $$
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(from_num +RAND()*(to_num -from_num+1));
RETURN i;
END$$
-- 假如要删除
-- drop function rand_num;
创建存储过程
插入角色表
-- 插入角色数据
DELIMITER $$
CREATE PROCEDURE insert_role(max_num INT)
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO role ( role_name,orders ) VALUES (rand_string(8),rand_num(1,5000));
UNTIL i = max_num
END REPEAT;
COMMIT;
END$$
-- 删除
-- DELIMITER ;
-- drop PROCEDURE insert_role;
插入用户表
-- 插入用户数据
DELIMITER $$
CREATE PROCEDURE insert_user(START INT, max_num INT)
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO user (username, role_id, password, salt ) VALUES (rand_string(8) ,rand_num(1,100000), rand_string(10), rand_string(10));
UNTIL i = max_num
END REPEAT;
COMMIT;
END$$
-- 删除
-- DELIMITER ;
-- drop PROCEDURE insert_user;
执行存储过程
-- 执行存储过程，往dept表添加10万条数据
CALL insert_role(100000);
-- 执行存储过程，往emp表添加100万条数据，编号从100000开始
CALL insert_user(100000,1100000);
mysql 1197_mysql主从不同步报错Last_Errno 1197
04使用 Navicat自带的数据生成
接来我们使用 Navicat的数据生成
直接下一步, 然后选择对应的两张表生成行数和对应的生成规则, 基于之前的执行速度, 这次 role生成 1w数据, user生成 10w数据
对于字符串类型的字段, 我们可以设置他的随机数据生成器, 根据需要进行选择
但是如果是 姓名 那么就会让你选择是否唯一
数字的话会让你选择范围, 默认值等
等确定好了, 我们就可以点击右下角进行生成随机测试数据
操作过程报错
1）mysql 1197_mysql主从不同步报错Last_Errno 1197
解决办法：根据你的机器的内存大小适当增大参数max_binlog_cache_size参数文章来源：https://www.toymoban.com/news/detail-628141.html
#查看现在的大小：
1)查看全局的参数大小：
mysql> show GLOBAL variables like 'max_binlog_cache_size';
2)查看当前会话的参数的大小：
mysql> show session variables like 'max_binlog_cache_size';
#如果只是当前会话的小，只要
mysql> set session max_binlog_cache_size=18446744073709547520;
否则需要
mysql> set global binlog_cache_size=18446744073709547520;
mysql 1197_mysql主从不同步报错Last_Errno 1197-CSDN博客文章来源地址https://www.toymoban.com/news/detail-628141.html
到了这里，关于三个主流数据库（Oracle、MySQL和SQL Server）的“单表造数的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
