如何快速生成指定二进制位数的MySQL二进制表？

当然有简便方法！你可以借助MySQL的递归CTE（Common Table Expression）结合存储过程来实现，只需要指定二进制位数就能自动生成对应的表和数据。下面是具体的实现方案：

这个方法最省心，只需要调用存储过程并传入位数，就能自动创建表并填充所有对应数据。

首先创建存储过程：

DELIMITER //

CREATE PROCEDURE generate_binary_table(IN p_bit_count INT)
BEGIN
    DECLARE max_id BIGINT;
    DECLARE col_sql VARCHAR(2000);
    DECLARE i INT DEFAULT 1;
    
    -- 验证输入范围：支持1到64位（适配MySQL BIGINT的最大范围）
    IF p_bit_count < 1 OR p_bit_count > 64 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Bit count must be between 1 and 64';
    END IF;
    
    SET max_id = POWER(2, p_bit_count) - 1;
    
    -- 动态拼接二进制列的SQL片段
    SET col_sql = 'Id';
    WHILE i <= p_bit_count DO
        SET col_sql = CONCAT(col_sql, ', (Id >> ', (p_bit_count - i), ') & 1 AS b', i);
        SET i = i + 1;
    END WHILE;
    
    -- 生成创建表的SQL（表名包含位数，避免不同位数的表冲突）
    SET @create_table_sql = CONCAT(
        'DROP TABLE IF EXISTS binary_table_', p_bit_count, '; ',
        'CREATE TABLE binary_table_', p_bit_count, ' (',
        'Id BIGINT PRIMARY KEY, ',
        REPLACE(SUBSTRING(col_sql, 4), ' AS ', ' INT, '), ' INT',
        ')'
    );
    PREPARE create_stmt FROM @create_table_sql;
    EXECUTE create_stmt;
    DEALLOCATE PREPARE create_stmt;
    
    -- 用递归CTE生成所有Id并插入表中
    SET @insert_sql = CONCAT(
        'INSERT INTO binary_table_', p_bit_count, ' (', col_sql, ') ',
        'WITH RECURSIVE seq AS (',
        'SELECT 0 AS Id ',
        'UNION ALL ',
        'SELECT Id + 1 FROM seq WHERE Id < ', max_id, ')',
        'SELECT ', col_sql, ' FROM seq'
    );
    PREPARE insert_stmt FROM @insert_sql;
    EXECUTE insert_stmt;
    DEALLOCATE PREPARE insert_stmt;
    
    SELECT CONCAT('Table binary_table_', p_bit_count, ' generated successfully with ', max_id + 1, ' rows') AS result;
END //

DELIMITER ;

使用方式 #

比如要生成3位二进制的表，只需执行：

CALL generate_binary_table(3);

执行后会创建名为binary_table_3的表，查询表内容：

SELECT * FROM binary_table_3;

就能得到你想要的结果：

如果你不想创建存储过程，也可以直接编写递归CTE查询，快速得到结果，或者将结果存入临时表：

比如生成3位二进制数据的查询：

WITH RECURSIVE seq AS (
    SELECT 0 AS Id
    UNION ALL
    SELECT Id + 1 FROM seq WHERE Id < 7 -- 2^3 - 1 = 7
)
SELECT 
    Id,
    (Id >> 2) & 1 AS b1, -- 提取第3位（最高位）
    (Id >> 1) & 1 AS b2, -- 提取第2位
    (Id >> 0) & 1 AS b3  -- 提取第1位（最低位）
FROM seq;

如果要将结果保存为表，只需加上CREATE TABLE：

CREATE TABLE binary_table_3 AS
WITH RECURSIVE seq AS (
    SELECT 0 AS Id
    UNION ALL
    SELECT Id + 1 FROM seq WHERE Id < 7
)
SELECT 
    Id,
    (Id >> 2) & 1 AS b1,
    (Id >> 1) & 1 AS b2,
    (Id >> 0) & 1 AS b3
FROM seq;

• 递归CTE：用来生成从0到2^n -1的连续Id序列（n是指定的二进制位数），覆盖所有可能的二进制组合。
• 位运算：Id >> k将Id的二进制值右移k位，再通过& 1提取最低位，从而得到对应位置的二进制位值。比如(Id >> 2) &1就是提取二进制数的最高位（3位情况下的第3位）。

内容的提问来源于stack exchange，提问作者sarah_golf