将mysql字段类型从INT更改为VARCHAR将如何影响以前存储为INT的数据 [英] How will changing the mysql field type from INT to VARCHAR affect data previously stored as an INT

问题描述

我需要将生产数据库中的字段从INT更新为VARCHAR.

I need to update a field in my production db from INT to VARCHAR.

当前INT字段中的信息敏感，我想确保在更改表类型时不要无意间更改或破坏它.例如，用户可能在此列中存储了INT 123456，而我想确保将其转换为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.

为完成此操作，推荐的过程是什么?

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.

推荐答案

在更改列类型之前，请确保MySQL服务器进入严格模式，并确保varchar(n)列具有足够大的n来容纳所有将整数转换为字符串时的整数.如果您不处于严格模式，则MySQL将静默截断数据以使其适合您的字符串大小:

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:

如果未启用严格的SQL模式，并且您为CHAR或VARCHAR列分配的值超过了该列的最大长度，则该值将被截断以适合并生成警告.对于非空格字符的截断，可以通过使用严格的SQL模式导致发生错误(而不是警告)并抑制该值的插入.

If strict SQL mode is not enabled and you assign a value to a CHAR or VARCHAR column 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.

但是，如果您进入严格模式首先:

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 ...

如果您的整数不完全适合您的varchar.

if your integers don't all fit in your varchar.

当然，在尝试更改表之前，您将获得数据库的经过验证的全新备份.通过 verified 验证，是指您已成功将备份还原到测试数据库中.

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.

这篇关于将mysql字段类型从INT更改为VARCHAR将如何影响以前存储为INT的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！