# 有索引的 表 delimiter // CREATE PROCEDURE `proc_students`() Begin Declare n int default 1; while n<=500000 do Insert into students values(n, concat('zhang san',n),floor(1+rand()*2),floor(1+rand()*4)); Set n=n+1; End while; End; // delimiter ; # 没有索引的 表 delimite...
explain select count(*) from students a inner join dept b on a.dept_>explain select count(*) from students_noindex a inner join dept b on a.dept_>select SQL_NO_CACHE count(*) from students a inner join dept b on a.dept_>select SQL_NO_CACHE count(*) from students_noindex a inner...
range:索引范围扫描,对索引的扫描开始于某一点,返回匹配值域的行,常见于between、and ,in, <、 >等的查询 unique_subquery:用于where中的in形式子查询,子查询返回不重复值唯一值 index_subquery:用于in形式子查询使用到了辅助索引或者in常数列表,子查询可能返回重复值,可以使用索引将子查询去重 ref:非唯一性索引扫...
AI代码解释 DELIMITER//CREATEDEFINER=`root`@`localhost`PROCEDURE`insert_table_s2`(INmin_numINT(10),INmax_numINT(10))BEGINDECLAREiINTDEFAULT0;SETautocommit=0;REPEATSETi=i+1;INSERTINTOs2VALUES((min_num+i),rand_string2(6),(min_num+30*i+5),rand_string2(6),rand_string2(10),rand_string2...
Kingbase中,auto_explain 是对explain强有力的补充,explain配合analyze、buffers等命令查看sql语句执行计划在一般场景下已经足够, 但是procedure、function里的SQL则无法分析到,配置auto_explain将自动在日志中输出procedure、function里每条SQL语句的执行计划结果,从而避免了手动去执行explain。
drop procedure if exists insert_emp;delimiter ;;create procedure insert_emp()begindeclare i int;set i=1;while(i<=100000)doinsert into employees(name,age,position) values(CONCAT('zhenghuisheng',i),i,'dev');set i=i+1;end while;end;;delimiter ;call insert_emp(); ...
为了方便本次没有使用SQL语句,而是使用存储过程创建数据,简单快速也方便。 # 创建 存储过程 create procedure insert_user_data() begin declare i int ; declare name varchar(20); declare phone_num varchar(11); set @SURNAME = '王李张刘陈杨黄赵吴周徐孙马朱胡郭何高林罗郑梁谢宋唐位许韩冯邓曹彭曾...
DELIMITER // CREATE PROCEDURE insert_s1 (IN min_num INT (10), IN max_num INT(10)) BEGIN DECLARE i INT DEFAULT 0; SET autocommit = 0; REPEAT SET i = i + 1; INSERT INTO s1 VALUES( (min_num + i), rand_string(6), (min_num + 30* i + 5), rand_string(6), rand_string(...
1CREATE DEFINER=`root`@`localhost` PROCEDURE `generateBigDataGroup`(IN num INT) 2BEGIN 3 #Routine body goes here... 4 DECLARE i INT DEFAULT 1; 5 6 #当i小于传入的参数时执行循环插入 7 WHILE i <= num DO 8 INSERT INTO `big_data_group`(`group_code`, `number_of_people`, `gmt_crea...
CREATE TABLE `t3` ( `id`intNOTNULL, `a`intDEFAULTNULL, `b`intDEFAULTNULL, PRIMARY KEY (`id`), KEY `a` (`a`) ) ENGINE=InnoDB; delimiter ;;create procedureidata()begin declare iint;seti=1;while(i<=100000)doinsert into t3values(i,i,i);seti=i+1; endwhile; end;; delimiter ;...