mysql的procedure不支持数组参数,在网上找了个强人的代码,与大家分享。
http://www.phpx.com/happy/thread-111759-1-1.html
DELIMITER ;
DROP DATABASE IF EXISTS PRO;
USE MYSQL
CREATE DATABASE PRO;
USE PRO
DROP TABLE IF EXISTS TEMP;
CREATE TABLE TEMP (
ID INT(8) NOT NULL AUTO_INCREMENT,
FOREIGN_KEY INT(8) NOT NULL,
PRIMARY KEY (ID)
) TYPE=MyISAM AUTO_INCREMENT=1 COMMENT='测试';
DELIMITER ;
DELIMITER //
DROP PROCEDURE IF EXISTS DNA //
CREATE PROCEDURE DNA (IN THE_CNT INT(8), IN THE_STR VARCHAR(10000))
BEGIN
DECLARE i INT(8) DEFAULT 0;
REPEAT SET i = i + 1;
INSERT INTO TEMP VALUES (NULL, SUBSTRING_INDEX(SUBSTRING_INDEX(THE_STR, ',', i), ',', -1));
UNTIL i >= THE_CNT END REPEAT;
END
//
DELIMITER ;
CALL DNA(7, '231,24,1114,151,7831241,9134,989');
SELECT * FROM TEMP;
+----+-------------+
| ID | FOREIGN_KEY |
+----+-------------+
| 1 | 231 |
| 2 | 24 |
| 3 | 1114 |
| 4 | 151 |
| 5 | 7831241 |
| 6 | 9134 |
| 7 | 989 |
+----+-------------+