作成SQL
DROP PROCEDURE IF EXISTS exec1;
DELIMITER ;;
CREATE PROCEDURE exec1(
IN SQL_VARCHAR TEXT,
IN LOOP_COUNT_MAX INT,
IN VAL TEXT
)
BEGIN
DECLARE LOOP_COUNT INT DEFAULT 1;
SET @STMT = SQL_VARCHAR;
PREPARE STMT FROM @STMT;
WHILE LOOP_COUNT < LOOP_COUNT_MAX
DO
SET @P1 = SUBSTRING_INDEX(SUBSTRING_INDEX(VAL, ':', LOOP_COUNT), ':', -1);
EXECUTE STMT USING @P1;
SET LOOP_COUNT = LOOP_COUNT + 1;
END WHILE;
END;
;;
DELIMITER ;
サンプル用
DROP TABLE IF EXISTS `prefs`;
CREATE TABLE `prefs` (
`id` int(2) ZEROFILL PRIMARY KEY AUTO_INCREMENT ,
`name` varchar(250) NOT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=UTF8 COMMENT='都道府県テーブル';
CALL exec1('INSERT INTO prefs (name) values (?)', 48, '北海道:青森:岩手:宮城:秋田:山形:福島:東京:神奈川:埼玉:千葉:茨城:栃木:群馬:山梨:新潟:長野:富山:石川:福井:愛知:岐阜:静岡:三重:大阪:兵庫:京都:滋賀:奈良:和歌山:鳥取:島根:岡山:広島:山口:四国:徳島:香川:愛媛:高知:福岡:佐賀:長崎:熊本:大分:宮崎:鹿児島:沖縄:未指定');
SELECT * FROM `prefs`;
| id | name | created | modified |
|---|---|---|---|
| 01 | 北海道 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 02 | 青森 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 03 | 岩手 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 04 | 宮城 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 05 | 秋田 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 06 | 山形 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 07 | 福島 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 08 | 東京 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 09 | 神奈川 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 10 | 埼玉 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 11 | 千葉 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 12 | 茨城 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 13 | 栃木 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 14 | 群馬 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 15 | 山梨 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 16 | 新潟 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 17 | 長野 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 18 | 富山 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 19 | 石川 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 20 | 福井 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 21 | 愛知 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 22 | 岐阜 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 23 | 静岡 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 24 | 三重 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 25 | 大阪 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 26 | 兵庫 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 27 | 京都 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 28 | 滋賀 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 29 | 奈良 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 30 | 和歌山 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 31 | 鳥取 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 32 | 島根 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 33 | 岡山 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 34 | 広島 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 35 | 山口 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 36 | 徳島 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 37 | 香川 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 38 | 愛媛 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 39 | 高知 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 40 | 福岡 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 41 | 佐賀 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 42 | 長崎 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 43 | 熊本 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 44 | 大分 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 45 | 宮崎 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 46 | 鹿児島 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |
| 47 | 沖縄 | 2014-07-18 16:43:39 | 2014-07-18 16:43:39 |