집 >데이터 베이스 >MySQL 튜토리얼 >Mysql向表中循环插入数据_MySQL
<code class=" hljs sql">begin for i in 1..1000 loop insert .. end loop; end; /
但是Mysql中不支持匿名块
百度了一下,方法如下:
delimiter的作用:告诉解释器,这段命令是否已经结束了,mysql是否可以执行了
默认情况下,delimiter是‘;’但是当我们编写procedure时,如果是默认设置,那么一遇到‘;’,mysql就要执行,这是我们不希望看到的
所以我们手动设置delimiter为//
<code class=" hljs cs">delimiter // create procedure per2() begin declare num int; set num=1; while num < 1000 do insert into per2(name) values(concat("fan", num)); set num=num+1; end while; end //
其中concat(“fan”, num),相当于oracle中fan||i的拼接效果,但是mysql不支持这样拼接
之后我们要调用这个procedure,才会插入数据
<code class=" hljs haml">(mysql@localhost) [fandb]> call per2(); -> // Query OK, 1 row affected (0.39 sec)
由于我没有将delimiter’改回来,所以输入‘;’后并没有执行,还需要//
<code class=" hljs asciidoc">(mysql@localhost) [fandb]> select name from mysql.proc where db='fandb' and type='procedure'; +------+ | name | +------+ | per2 | +------+ 1 row in set (0.00 sec)
<code class=" hljs vbnet">(mysql@localhost) [fandb]> show create procedure per2\G *************************** 1. row *************************** Procedure: per2 sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION Create Procedure: CREATE DEFINER=`mysql`@`localhost` PROCEDURE `per2`() begin declare num int; set num=1; while num < 1000 do insert into per2(name) values(concat("fan", num)); set num=num+1; end while; end character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: utf8_general_ci 1 row in set (0.00 sec)
while ·· end while:
<code class=" hljs oxygene">mysql > DELIMITER // mysql > CREATE PROCEDURE proc4() -> begin -> declare var int; -> set var=0; -> while var<6 do -> insert into t values(var); -> set var=var+1; -> end while; -> end; -> //
? while 条件 do–循环体 endwhile
?repeat ·· end repeat:
它在执行操作后检查结果,而 while 则是执行前迚行检查。
<code class=" hljs haml">mysql > DELIMITER // mysql > CREATE PROCEDURE proc5 () -> begin -> declare v int; -> set v=0; -> repeat -> insert into t values(v); -> set v=v+1; -> until v>=5 -> end repeat; -> end; -> //
? repeat–循环体 until 循环条件 endrepeat;
loop ·· endloop:
loop 循环丌需要初始条件,这点和 while 循环相似,同时和 repeat 循环一样丌需要结束条
件, leave 诧句的意义是离开循环。
<code class=" hljs haml">mysql > DELIMITER // mysql > CREATE PROCEDURE proc6 () -> begin -> declare v int; -> set v=0; -> LOOP_LABLE:loop -> insert into t values(v); -> set v=v+1; -> if v >=5 then -> leave LOOP_LABLE; -> end if; -> end loop; -> end; -> //
LABLES 标号:
标号可以用在 begin repeat while 戒者 loop 诧句前,诧句标号叧能在合法的诧句前面使用。
可以跳出循环,使运行指令达到复合诧句的最后一步。