在MySQL中批量更改列类型

Question

我有一个PHP脚本，用户在其中创建调查问卷，并在数据库中的脚本表中存储传入的数据。这个网站已经上线一段时间了，数据库中大约有100个表。

我的脚本有很大的缺陷！它在我真正需要"text“的地方调用了"tinytext”字段。有没有办法将所有tinytext列批量更新为text？

谢谢!

Answer 1

没有存储过程的解决方案(仅使用phpMyAdmin或任何其他数据库管理员工具)。

运行以下查询

SELECT 
    CONCAT('ALTER TABLE ',
            TABLE_NAME,
            ' CHANGE COLUMN ',
            COLUMN_NAME,
            ' ',
            column_name,
            ' TARGET_TYPE ',
            CASE
                WHEN IS_NULLABLE = 'NO' THEN ' NOT '
                ELSE ''
            END,
            ' NULL;') AS que
FROM
    information_schema.columns
WHERE
    table_schema = 'MY DB'
        AND data_type = 'SOURCE_TYPE';

此查询将返回要触发的所有语句。您可以运行它们或保存到SQL升级脚本中

示例(从tinyint到bit)：

ALTER TABLE mytable CHANGE COLUMN redacted redacted BIT  NULL;
ALTER TABLE mytable CHANGE COLUMN redacted2 redacted2 BIT NOT NULL;

Answer 2

一种方法是查找给定数据库中的所有tinytext列，然后为每一列创建ALTER TABLE语句。使用存储过程的工作解决方案：

DELIMITER $$

DROP PROCEDURE IF EXISTS `BULK_RETYPE` $$
CREATE PROCEDURE `BULK_RETYPE`(IN SCHEMA_NAME VARCHAR(255), IN FROM_TYPE VARCHAR(255), IN TO_TYPE VARCHAR(255))
BEGIN
    DECLARE `done` INT DEFAULT FALSE;
    DECLARE tn VARCHAR(255);
    DECLARE fn VARCHAR(255);
    DECLARE `cur1` CURSOR FOR
    SELECT
        `TABLE_NAME`,
    `COLUMN_NAME`
    FROM
        `information_schema`.`COLUMNS`
    WHERE
        `DATA_TYPE` = FROM_TYPE AND `TABLE_SCHEMA` = SCHEMA_NAME;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;


    OPEN cur1;
    read_loop: LOOP
        FETCH cur1 INTO `tn`, `fn`;
        IF done THEN
          LEAVE read_loop;
        END IF;

          SET @ALTER_SQL = CONCAT('ALTER TABLE ', '`', tn,'`' , ' MODIFY ', '`', fn,'`' , ' ', TO_TYPE);

          PREPARE stmt1 FROM @ALTER_SQL;
          EXECUTE stmt1;

    END LOOP;   
    CLOSE cur1; 
END $$

DELIMITER ;
CALL BULK_RETYPE('test', 'tinytext', 'text');

Answer 3

不完全是，但是如果你使用PHPMyAdmin，这是一个相当快的任务……或者，您可以导出需要更改的所有表和字段的列表，并将必要的ALTER TABLE语句放在一起，然后执行它们。