更改整个数据库中列的数据类型-MySQL [英] Change the Datatype of a Column in the entire Database - MySQL

问题描述

我是MySQL的新手.我设计了一些具有Char数据类型的表.现在，我想将所有char数据类型列更改为具有Char中提到的大小的Varchar.

I am new to MySQL. I have designed few tables with Char datatype. Now I want to change all the char datatype columns to Varchar with respective size mentioned in Char.

For example.
Existing - Phone Char(10) 
Expected - Phone Varchar(10)

大约有50多张桌子.我知道如何更改每个表和每个列的数据类型，但是有什么更好的方法可以一次更改数据类型.

There are around 50 Plus tables. I know how to alter the datatype for each and every table and column but is there any better way to change the datatype in a single shot.

推荐答案

如果您无法编写脚本来完成这项工作，请编写脚本来编写脚本来完成工作！

If you can't write a script to do the job, write a script that writes a script to do the job!

您想要一堆ALTER TABLE语句:

You want a bunch of ALTER TABLE statements:

SET SESSION group_concat_max_len = 1000000; -- Bumps the limit of 1028
SELECT GROUP_CONCAT(
    CONCAT(
        'ALTER TABLE `',
        table_name,
        '` MODIFY COLUMN `',
        column_name,
        '` VARCHAR(',
        character_maximum_length,
        ')'
    )
SEPARATOR ';\n') your_alter_statements
FROM information_schema.columns
WHERE table_schema = 'concrete'
AND data_type = 'char';

这将导致:

ALTER TABLE `table1` MODIFY COLUMN `col1` VARCHAR(10);
ALTER TABLE `table1` MODIFY COLUMN `col2` VARCHAR(10);
ALTER TABLE `table2` MODIFY COLUMN `col1` VARCHAR(10);
ALTER TABLE `table3` MODIFY COLUMN `col1` VARCHAR(10);
ALTER TABLE `table3` MODIFY COLUMN `col2` VARCHAR(10);

运行它，您就可以早点回家！

Run that and you can go home early!

更新:通过添加group_concat_max_len停止了截断.根据列的长度动态调整长度.

UPDATE: Stopped the truncation by adding group_concat_max_len. Made the length dynamic based on the columns length.

这篇关于更改整个数据库中列的数据类型-MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！