How to change MySQL table without data loss?

Question

In my application, I make some changes and upload them to the test server. Since I do not have access to the server database, I run ALTER commands to make changes to it.

Using the method, I ran the following command on the server:

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

After that, I found that all the data in the table was deleted. Now the table is empty.

Therefore, I need to change the table without deleting its data. Is there any way to do this?

Answer 1

Your question is completely obvious. You add a new column to the table and set it to NOT NULL .
To make things clearer, I will explain the server response when I run the command:

• You add a new column, so each row in the table should set a value for this column.

• Since you are not declaring any default value, all rows set to null for this new column.

• The server notices that the rows in the table are null in the column, which does not allow null s. It is illegal.

• To resolve the conflict, invalid rows were deleted .

There are several good fixes for this problem:

• Set the default value (recommended) for the column to be created.

• Create a column without NOT NULL , set the appropriate values, and then create a NOT NULL column.

Answer 2

You can create a temporary table, transfer all the information from the table that you want to change, and then return the information to the changed table.