I need to update a field in my production db from INT to VARCHAR.
我需要将生产db中的一个字段从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.
谢谢你的帮助。
1 个解决方案
#1
43
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 n to 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 mode first:
但如果你首先进入严格的模式:
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 verified I mean that you have successfully restored your backup into a test database.
当然，在尝试更改表之前，您将对数据库进行新的验证备份。通过验证，我的意思是您已经成功地将备份恢复到一个测试数据库中。
#1
43
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 n to 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 mode first:
但如果你首先进入严格的模式:
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 verified I mean that you have successfully restored your backup into a test database.
当然，在尝试更改表之前，您将对数据库进行新的验证备份。通过验证，我的意思是您已经成功地将备份恢复到一个测试数据库中。