MySQL: Adding column to existing table. Have the values ready, how do I enter them altogether?-CodePudding

I have table with data on old game characters. I'd like to add a gender column. If I do

   ALTER TABLE characters
   ADD gender ENUM('m','f') AFTER char_name

then I get a column full of NULLs. How do I get the values in? Using an INSERT statement tries to tag them all into new rows, instead of replacing the NULLs. Using an UPDATE statement requires a new statement for every single entry.

Is there any way to just drop a "VALUES ('m'),('f'),('f'),('m'),('f') etc" into the ALTER statement or anything else and update them all efficiently?

CodePudding user response：

There is no way to fill in specific values during ALTER TABLE. The value will be NULL or else a default value you define for the column.

You may find INSERT...ON DUPLICATE KEY UPDATE is a convenient way to fill in the values.

Example:

CREATE TABLE characters (
  id serial primary key,
  char_name TEXT NOT NULL
);

INSERT INTO characters (char_name) VALUES
 ('Harry'), ('Ron'), ('Hermione');

SELECT * FROM characters;
 ---- ----------- 
| id | char_name |
 ---- ----------- 
|  1 | Harry     |
|  2 | Ron       |
|  3 | Hermione  |
 ---- -----------

Now we add the gender column. It will add the new column with NULLs.

ALTER TABLE characters 
  ADD gender ENUM('m','f') AFTER char_name;

SELECT * FROM characters;

 ---- ----------- -------- 
| id | char_name | gender |
 ---- ----------- -------- 
|  1 | Harry     | NULL   |
|  2 | Ron       | NULL   |
|  3 | Hermione  | NULL   |
 ---- ----------- --------

Now we update the rows:

INSERT INTO characters (id, char_name, gender) VALUES
  (1, '', 'm'), (2, '', 'm'), (3, '', 'f')
ON DUPLICATE KEY UPDATE gender = VALUES(gender);

It looks strange to use '' for the char_name, but it will be ignored anyway, because we don't set it in the ON DUPLICATE KEY clause. The original char_name is preserved. Specifying the value in the INSERT is necessary only because the column is defined NOT NULL and has no DEFAULT value.

SELECT * FROM characters;

 ---- ----------- -------- 
| id | char_name | gender |
 ---- ----------- -------- 
|  1 | Harry     | m      |
|  2 | Ron       | m      |
|  3 | Hermione  | f      |
 ---- ----------- --------

DBFiddle