将 mysql 字段类型从 INT 更改为 VARCHAR 将如何影响以前存储为 INT 的数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7465601/
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 will changing the mysql field type from INT to VARCHAR affect data previously stored as an INT
提问by john k
I need to update a field in my production db from INT to VARCHAR.
我需要将生产数据库中的一个字段从 INT 更新为 VARCHAR。
The information in the current INT field is sensitive and I want to ensure that I don't alter it inadvertently or destroy it while changing the table type. For example, a user may have INT 123456 stored in this column and I want to ensure that is accurately stored once converted to VARCHAR.
当前 INT 字段中的信息是敏感的，我想确保在更改表类型时不会无意中更改它或破坏它。例如，用户可能将 INT 123456 存储在此列中，我想确保在转换为 VARCHAR 后准确存储。
What is the recommended process for accomplishing this?
完成此操作的推荐流程是什么？
Is there anything I need to worry about when using something like this?
使用这样的东西时我需要担心什么吗？
ALTER TABLE table_sample CHANGE col_sample col_sample VARCHAR;
Thanks in advance for any help.
在此先感谢您的帮助。
回答by mu is too short
Get your MySQL server into strict mode before you change the column type and make sure that your varchar(n)column has a large enough nto hold all of the integers when they're converted to strings. If you're not in strict mode then MySQL will silently truncate your data to fit your string size:
在更改列类型之前，让您的 MySQL 服务器进入严格模式，并确保您的varchar(n)列有足够大的空间n来容纳所有转换为字符串的整数。如果您不是在严格模式下，那么 MySQL 会以静默方式截断您的数据以适合您的字符串大小：
If strict SQL mode is not enabled and you assign a value to a
CHARorVARCHARcolumn that exceeds the column's maximum length, the value is truncated to fit and a warning is generated. For truncation of nonspace characters, you can cause an error to occur (rather than a warning) and suppress insertion of the value by using strict SQL mode.
如果未启用严格 SQL 模式并且您为
CHAR或VARCHAR列分配的值超过该列的最大长度，则该值将被截断以适合并生成警告。对于非空格字符的截断，您可以使用严格的 SQL 模式导致发生错误（而不是警告）并禁止插入值。
But if you get into strict modefirst:
但是如果你先进入严格模式：
mysql> set sql_mode = 'STRICT_ALL_TABLES';
mysql> alter table table_sample change col_sample col_sample varchar(6);
You'll get a nice error message like this:
你会得到一个很好的错误消息，如下所示：
ERROR 1406 (22001): Data too long for column 'col_sample' at row ...
if your integers don't all fit in your varchar.
如果您的整数不适合您的varchar.
And, of course, you will have a fresh verified backup of your database before you try to change the table. And by verifiedI mean that you have successfully restored your backup into a test database.
而且，当然，在尝试更改表之前，您将拥有一个经过验证的全新数据库备份。通过验证，我的意思是您已成功将备份恢复到测试数据库中。
