MySQL 插入隨機字符串測試資料實戰：自定義函數生成隨機串、預存過程批量寫入、Limit 隨機採樣優化

測試環境需大量隨機資料。本文提供：自定義函數生成隨機字符串、預存過程批量插入、業務場景：論文引用隨機補充 及 LIMIT 隨機採樣效能優化演進。

一、建表

CREATE TABLE `tables_a` (
  `id` INT(10) NOT NULL DEFAULT '0',
  `name` CHAR(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

二、建立隨機字符串函數

SET GLOBAL log_bin_trust_function_creators = 1;

DROP FUNCTION IF EXISTS rand_string;
DELIMITER //
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
  DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
  DECLARE return_str VARCHAR(255) DEFAULT '';
  DECLARE i INT DEFAULT 0;
  WHILE i < n DO
    SET return_str = CONCAT(return_str, SUBSTRING(chars_str, FLOOR(1 + RAND() * 62), 1));
    SET i = i + 1;
  END WHILE;
  RETURN return_str;
END //
DELIMITER ;

三、建立批量插入預存過程

DELIMITER //
CREATE PROCEDURE test_insert(IN x INT, IN y INT, IN z INT)
BEGIN
  DECLARE i INT DEFAULT x;
  WHILE i < y DO
    INSERT INTO tables_a VALUES (i, rand_string(z));
    SET i = i + 1;
  END WHILE;
END //
DELIMITER ;

呼叫：CALL test_insert(1, 10001, 20); 插入 1 萬條 20 位隨機串。

四、業務場景：DBLP 論文引用隨機補充

需為每篇論文隨機添加 20 條引用關係。

方案 1：ORDER BY RAND() LIMIT（極慢）

INSERT INTO citation (pId1, pId2)
VALUES (
  (SELECT pId FROM papers LIMIT ?, 1),
  (SELECT pId FROM papers LIMIT ?, 1)
);
-- 15 萬表，插 10 萬條耗時 22 分鐘

方案 2：增加自增臨時列 + 等值查詢（快 35 倍）

ALTER TABLE papers ADD COLUMN temp INT AUTO_INCREMENT PRIMARY KEY;
-- 應用層生成 20 個不重複隨機數 0~N
INSERT INTO citation (pId1, pId2)
VALUES (
  (SELECT pId FROM papers WHERE temp = ?),
  (SELECT pId FROM papers WHERE temp = ?)
);
-- 10 萬條耗時 38 秒

五、MySQL 隨機採樣優化演進

問題

SELECT * FROM t ORDER BY RAND() LIMIT 5 全表掃描 + 檔案排序，15 萬表耗 8 秒+。

優化 1：JOIN + MAX(id) * RAND()

SELECT * FROM t AS t1
JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM t)) AS id) AS t2
WHERE t1.id >= t2.id ORDER BY t1.id ASC LIMIT 5;
-- 產生連續 5 行，非真正隨機

優化 2：子查詢 + FLOOR(MAX * RAND())

SELECT * FROM t
WHERE id >= (SELECT FLOOR(MAX(id) * RAND()) FROM t)
ORDER BY id LIMIT 1;
-- 0.5 秒

優化 3：內聯子查詢（最快）

SELECT * FROM t
WHERE id >= (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM t)))
ORDER BY id LIMIT 1;
-- 0.01 秒

優化 4：加 MIN(id) 修正偏移

SELECT * FROM t
WHERE id >= (
  SELECT FLOOR(
    RAND() * ((SELECT MAX(id) FROM t) - (SELECT MIN(id) FROM t))
    + (SELECT MIN(id) FROM t)
  )
)
ORDER BY id LIMIT 1;

終極版：JOIN 形式（穩定 0.015 秒）

SELECT * FROM t AS t1
JOIN (
  SELECT ROUND(
    RAND() * ((SELECT MAX(id) FROM t) - (SELECT MIN(id) FROM t))
    + (SELECT MIN(id) FROM t)
  ) AS id
) AS t2
WHERE t1.id >= t2.id ORDER BY t1.id LIMIT 1;

六、PHP 壓測對比（10 次）

• 子查詢版：0.147 秒
• JOIN 版：0.015 秒（**快 10 倍**）

隨機採樣核心：**避免 ORDER BY RAND()，利用主鍵範圍 + RAND() 計算邊界，JOIN 形式最穩定**。