開発メモ

開発用のメモです。

MySQL 2つの必須項目をもつテーブルのデータを手早く作る

delimiter $
DROP PROCEDURE IF EXISTS exec2$
CREATE PROCEDURE exec2(
    IN SQL_VARCHAR TEXT, 
    IN LOOP_COUNT_MAX INT, 
    IN VAL1 TEXT,
    IN VAL2 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(VAL1, ':', LOOP_COUNT), ':', -1);
        SET @P2 = SUBSTRING_INDEX(SUBSTRING_INDEX(VAL2, ':', LOOP_COUNT), ':', -1);

        EXECUTE STMT USING @P1, @P2;
        SET LOOP_COUNT = LOOP_COUNT + 1;
    END WHILE;
END;
$

delimiter ;

CALL exec2(
    'UPDATE users SET pref_id = ? WHERE mod(id + FLOOR(RAND() * 10),47) = ? AND pref_id = 0', 
    48, 
    '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', 
    '0: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'
);

Twitter: @asahina_alice