MySQL 如何将 TEXT 类型的列转换为 VARCHAR？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13531230/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to convert a column of type TEXT to VARCHAR?
提问by user1768076
Is there a way to convert a column with data in it as TEXTto VARCHAR(X)easily? There are no existing records in the column is longer than X.
有没有一种办法，在有数据的列转换为TEXT以VARCHAR(X)容易吗？列中没有现有记录长于X。
回答by Shakti Singh
Yes for sure
是肯定的
ALTER TABLE table_name MODIFY column_name VARCHAR(X)
ALTER TABLE table_name MODIFY column_name VARCHAR(X)
回答by Hello World
For MS SQL:
对于 MS SQL：
ALTER TABLE table_name
ALTER COLUMN column_name datatype(value)
For MySQL
对于 MySQL
ALTER TABLE table_name
MODIFY column_name datatype(value)
回答by Andrew Zolotarev
I used this for mass change of my DB. Execute it, then copy all rows, paste into query field, remove header and run.
我用它来批量更改我的数据库。执行它，然后复制所有行，粘贴到查询字段中，删除标题并运行。
Change ENGINE, CHARSET and COLLATION of all tables in DB=itsd:
更改 DB=itsd 中所有表的 ENGINE、CHARSET 和 COLLATION：
SELECT CONCAT("ALTER TABLE ", TABLE_SCHEMA, '.', TABLE_NAME," ENGINE=INNODB,
CHARSET=utf8, COLLATE=utf8_general_ci;") AS ExecuteTheString
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="itsd"
AND TABLE_TYPE="BASE TABLE";
Convert CHARSET and COLLATION of all tables in DB=itsd:
转换 DB=itsd 中所有表的 CHARSET 和 COLLATION：
SELECT CONCAT("ALTER TABLE ", TABLE_SCHEMA, '.', TABLE_NAME," CONVERT TO
CHARACTER SET utf8;") AS ExecuteTheString
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="itsd"
AND TABLE_TYPE="BASE TABLE";
Change all TEXT fields to VARCHAR(255) of all tables in DB=itsd:
将 DB=itsd 中所有表的所有 TEXT 字段更改为 VARCHAR(255)：
SELECT CONCAT('ALTER TABLE ', table_schema, '.', table_name, ' MODIFY COLUMN ',
column_name, ' VARCHAR(255);')
FROM information_schema.columns
WHERE TABLE_SCHEMA = 'itsd'
AND DATA_TYPE = 'text'
AND TABLE_TYPE="BASE TABLE";
