例如有三张表
A(a_id,a_name,b_id)
B(b_id,c_id)
C(c_id,c_name,等等)
我想写个存储过程,前台一般传来都是
a_id = 1,
a_name = name,
b_id = 1,
c_id = 1,2,3,4,5
之类的值,我想做到同时给2张表插入数据
A表插入的是(a_id=1,a_name=name,b_id=1)
B表插入的是(b_id=1,c_id =1),(b_id=1,c_id =2),(b_id=1,c_id =3),(b_id=1,c_id =4),(b_id=1,c_id =5)
我现在的问题是
1、如何才能在存储过程里判断c_id传来的个数。
2、如何做到循环插入数据。
A(a_id,a_name,b_id)
B(b_id,c_id)
C(c_id,c_name,等等)
我想写个存储过程,前台一般传来都是
a_id = 1,
a_name = name,
b_id = 1,
c_id = 1,2,3,4,5
之类的值,我想做到同时给2张表插入数据
A表插入的是(a_id=1,a_name=name,b_id=1)
B表插入的是(b_id=1,c_id =1),(b_id=1,c_id =2),(b_id=1,c_id =3),(b_id=1,c_id =4),(b_id=1,c_id =5)
我现在的问题是
1、如何才能在存储过程里判断c_id传来的个数。
2、如何做到循环插入数据。
2 自己动手做一下吧,有问题再问
mysql> select length(replace('1,2,3,4,5',',',''));
+-------------------------------------+
| length(replace('1,2,3,4,5',',','')) |
+-------------------------------------+
| 5 |
+-------------------------------------+
1 row in set (0.00 sec)mysql>得到个数。
-> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
-> 'mysql.com'
insert into seq values
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9),
(10),
(11),
(12),
(13),
(14),
(15),
(16),
(17),
(18),
(19),
(20),
(21),
(22),
(23),
(24),
(25),
(26),
(27),
(28),
(29),
(30),
(31),
(32),
(33),
(34),
(35),
(36),
(37),
(38),
(39),
(40),
(41),
(42),
(43),
(44),
(45),
(46),
(47),
(48),
(49),
(50);然后
insert into B(b_id,c_id)
select 1,id
from seq
where find_in_set(id,'1,2,3,4,5');mysql> select 1,id
-> from seq
-> where find_in_set(id,'1,2,3,4,5');
+---+----+
| 1 | id |
+---+----+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 1 | 5 |
+---+----+
5 rows in set (0.00 sec)mysql>
SUBSTRING_INDEX(SUBSTRING_INDEX('1,2,3,4,5',',',2),',',-1),
SUBSTRING_INDEX(SUBSTRING_INDEX('1,2,3,4,5',',',3),',',-1),
SUBSTRING_INDEX(SUBSTRING_INDEX('1,2,3,4,5',',',4),',',-1),
SUBSTRING_INDEX(SUBSTRING_INDEX('1,2,3,4,5',',',5),',',-1);
这样,就把1,2,3,4,5拆成单个数字了。