创建存储过程批量插入数据

              什么是存储过程，简单来说存储过程是一组为了完成特定功能的的SQL语句集。先看脚本：

#! /bin/bash

host="xxx"
port="xxx"
userName="xxx"
password="xxx"
dbname="xxx"
dbset="--default-character-set=utf8 -A"


/usr/local/mysql/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port}   -e "source test.sql"

再看test.sql：

delimiter $
create procedure ac()
begin
declare i int;
set i=1;
while  i<10000  do
insert into mapan values(i);
set  i=i+1;
end while ;
end $
call ac()$

create procedure是创建一个存储过程，ac是存储过程的名字。

执行脚本文件：

| 9987 |
| 9988 |
| 9989 |
| 9990 |
| 9991 |
| 9992 |
| 9993 |
| 9994 |
| 9995 |
| 9996 |
| 9997 |
| 9998 |
| 9999 |
+------+
9999 rows in set (0.00 sec)

mysql> show procedure status;
+------+------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db   | Name | Type      | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------+------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| test | ac   | PROCEDURE | root@localhost | 2018-03-08 09:06:31 | 2018-03-08 09:06:31 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+------+------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

mysql> 

查询表可以看到插入了9999行数据，还可以看到我们创建的存储过程。