建立測試用 流水號Table

這個存儲過程名為 createNumberTable，用於創建一個包含數字的記憶體表（sequence_number），以便生成假數據或進行測試。

以下是對這個存儲過程的註解和方法說明：

# Create a number table for generating fake or test data.
DROP PROCEDURE IF EXISTS createNumberTable;
DELIMITER //
CREATE PROCEDURE createNumberTable()
BEGIN
    DROP TABLE if exists local_test.sequence_number;
    CREATE TABLE local_test.sequence_number
    (
        id INT UNSIGNED NOT NULL PRIMARY KEY
    ) ENGINE = Memory;

# add id 0 ~ 100 row data
    INSERT INTO sequence_number(id)
    SELECT @num := @num + 1
    FROM information_schema.INNODB_BUFFER_PAGE a
       , (SELECT @num := 0) b
    LIMIT 100;

# add id 100 ~ 10000 row data
    INSERT INTO sequence_number(id)
    SELECT @num := @num + 1
    FROM sequence_number a
       , (SELECT id
          FROM sequence_number
          LIMIT 99) b
       , (SELECT @num := 100) c;

# check sequence_number data info
    SELECT COUNT(1)
         , MIN(id)
         , MAX(id)
    FROM sequence_number;
END //
DELIMITER ;

-- call procedure
CALL createNumberTable();

# According to the 'sequence_number' table, its maximum number of records is 10,000.
# So, if the limit is set to 10, the maximum number of inserted records is 100,000.
# If it's set to 20, the maximum number of inserted records is 200,000, and so forth(on).
INSERT INTO t_big_data_practise(name, content_m, content_t, create_date, type)
SELECT HEX(RAND() * 0xFFFFFFFF),
       concat(HEX(RAND() * 0xFFFFFFFF), HEX(RAND() * 0xFFFFFFFF)),
       HEX(RAND() * 0xFFFFFFFF),
       DATE_ADD('2023-01-01', INTERVAL RAND() * 364 DAY),
       LEFT(HEX(RAND() * 0xFFFFFFFF), 2)
FROM local_test.sequence_number a
         JOIN (SELECT id
               FROM local_test.sequence_number
               LIMIT 2000) b;

-- check result
SELECT FORMAT(COUNT(1), 0) AS cnt
     , MIN(create_date)
     , MAX(create_date)
FROM t_big_data_practise;

這個存儲過程的主要功能是創建 sequence_number表，並填充一系列數字值，包括從 0 到 100 和從 100 到 10,000 的數字。最後，它通過查詢返回了表中的總行數、最小值和最大值。這個表可以用於測試和生成數據，特別是在需要一個包含連續整數的表時。

而外補充 Postgres 寫法

目前還在熟悉寫法，很多地方還不是很完善，暫時先這樣吧....

DROP PROCEDURE IF EXISTS local_test.createNumberTable;
CREATE OR REPLACE PROCEDURE local_test.createNumberTable()
AS $$
BEGIN
    DROP TABLE IF EXISTS local_test.sequence_number;
    CREATE TABLE local_test.sequence_number
    (
        id integer NOT NULL PRIMARY KEY
    );

    INSERT INTO local_test.sequence_number(id)
    SELECT generate_series(1, 10000);

    RAISE NOTICE 'Number of rows: %', (SELECT COUNT(1) FROM local_test.sequence_number);
    RAISE NOTICE 'Minimum id: %', (SELECT MIN(id) FROM local_test.sequence_number);
    RAISE NOTICE 'Maximum id: %', (SELECT MAX(id) FROM local_test.sequence_number);
END;
$$ LANGUAGE plpgsql;

-- call procedure
call local_test.createNumberTable();