如何在不丢失数据的情况下更改MySQL表?

问题描述：

在我的应用程序中，我进行了一些更改并将其上传到测试服务器.因为我无权访问服务器数据库，所以我运行ALTER命令对其进行更改.

In my application, I make some changes and upload them to a testing server. Because I have no access to the server database I run ALTER commands to make changes on it.

使用一种方法，我在服务器上运行了以下命令:

Using a method I ran the following command on server:

ALTER TABLE `blahblahtable` ADD COLUMN `newcolumn` INT(12) NOT NULL

此后，我发现表的所有数据已被删除.现在表是空白的.

After that, I found that the all the data of the table has been removed. Now the table is blank.

因此，我需要更改表而无需删除他的数据.有什么办法吗?

So I need to alter the table without removing his data. Is there any way to do that?

答

您的问题很明显.您正在向表中添加新列，并将其设置为NOT NULL.
为了使事情更清楚，我将在运行命令时解释服务器的反应:

Your question is quite obvious. You're adding a new column to the table, and setting it to NOT NULL.
To make things clearer, I will explain the reaction of the server when you run the command:

您添加了新列，因此表的每行必须为此列设置一个值.

You add a new column, so every row of the table has to set a value for that column.

由于您没有声明任何默认值，因此所有行都为此新列设置了null.

As you don't declare any default value, all the rows set null for this new column.

服务器注意到表的行在不允许null的列上具有null值.这是非法的.

The server notices that the rows of the table have a null value on a column that doesn't allow nulls. This is illegal.

为解决冲突，将删除无效行.

To solve the conflict, the invalid rows are deleted.

此问题有一些不错的解决方法:

There are some good fixes for this issue:

为要创建的列设置默认值(推荐).

在没有NOT NULL的情况下创建列，设置适当的值，然后将列设置为NOT NULL.

Create the column without the NOT NULL, set the appropiate values, and then make the column NOT NULL.

如何在不丢失数据的情况下更改MySQL表?

相关推荐