CREATE TABLE `t_user` ( `id` int (11) NOT NULL AUTO_INCREMENT, `c_user_id` varchar (36) NOT NULL DEFAULT '' , `c_name` varchar (22) NOT NULL DEFAULT '' , `c_province_id` int (11) NOT NULL , `c_city_id` int (11) NOT NULL , `create_time` datetime NOT NULL , PRIMARY KEY (`id`), KEY `idx_user_id` (`c_user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE `t_user_memory` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c_user_id` varchar(36) NOT NULL DEFAULT '', `c_name` varchar(22) NOT NULL DEFAULT '', `c_province_id` int(11) NOT NULL, `c_city_id` int(11) NOT NULL, `create_time` datetime NOT NULL, PRIMARY KEY (`id`), KEY `idx_user_id` (`c_user_id`) ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4; |
可以直接在查询接口执行
delimiter $$ CREATE DEFINER=`root`@`%` FUNCTION `randStr`(n INT ) RETURNS varchar (255) CHARSET utf8mb4 DETERMINISTIC BEGIN DECLARE chars_str varchar (100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789' ; 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() * 62), 1)); SET i = i + 1; END WHILE; RETURN return_str; END $$ CREATE DEFINER=`root`@`%` FUNCTION `randDataTime`(sd DATETIME,ed DATETIME) RETURNS datetime DETERMINISTIC BEGIN DECLARE sub INT DEFAULT 0; DECLARE ret DATETIME; SET sub = ABS (UNIX_TIMESTAMP(ed)-UNIX_TIMESTAMP(sd)); SET ret = DATE_ADD(sd,INTERVAL FLOOR(1+RAND()*(sub-1)) SECOND ); RETURN ret; END $$ delimiter ; |
# 创建插入数据存储过程 CREATE DEFINER=`root`@`%` PROCEDURE `add_t_user_memory`( IN n int ) BEGIN DECLARE i INT DEFAULT 1; WHILE (i <= n) DO INSERT INTO t_user_memory (c_user_id, c_name, c_province_id,c_city_id, create_time) VALUES (uuid(), randStr(20), FLOOR(RAND() * 1000), FLOOR(RAND() * 100), NOW()); SET i = i + 1; END WHILE; END |
select @@version;
mysql8之前的授权
grant all privileges on *.* to root@'%';
mysql8授权
GRANT ALL ON *.* TO 'root'@'%';
刷新权限,让授权生效
flush privileges;
执行存储过程 CALL add_t_user_memory(10000000); |
CALL add_t_user_memory(10000000)
> 1114 - The table 't_user_memory' is full
于是就修改Mysql的配置文件my.ini,在[mysqld]下添加/修改两行:
tmp_table_size = 256M
max_heap_table_size = 256M
系统默认是16M,修改完后重启mysql