MySQL 如何为已使用的表添加 varchar 字段？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9013319/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to add a varchar field for a table already in use?
提问by user291701
I've got a mysql database with a table (InnoDB) of Games:
我有一个带有游戏表 (InnoDB) 的 mysql 数据库：
gamerooms
id: bigint(20) unsigned not null auto_increment
PRIMARY KEY (`id`)
I'd like to start generating a UUID value for each row which I can share publicly, something like:
我想开始为我可以公开共享的每一行生成一个 UUID 值，例如：
gamerooms
id | id_public |
--------------------
1 | abcde
2 | ghijk
3 | lmnop
...
select * from gamerooms where id_public = ...
How do I add this new column, also keeping in mind that there are already records in the table? I'm confused because the column should be marked NOT NULL, but after adding the column, all records that already exist would have empty values.. Do I have to provide a default value?:
我如何添加这个新列，还要记住表中已经有记录？我很困惑，因为该列应该被标记为 NOT NULL，但是在添加该列之后，所有已经存在的记录都会有空值。我是否必须提供一个默认值？：
ALTER TABLE `gamerooms` ADD COLUMN `id_public` varchar(36) DEFAULT something AFTER `id`
I want to put an index on id_public of course after it's created, so not sure if null values after the column is first created will mess anything up.
我当然想在创建 id_public 之后在 id_public 上放置一个索引，所以不确定第一次创建列后的空值是否会搞砸任何事情。
Also, I can use varchar(36) with mysqls UUID() output, right?
另外，我可以将 varchar(36) 与 mysqls UUID() 输出一起使用，对吗？
Thank you
谢谢
回答by Jonah Bishop
Your ALTERstatement is correct:
你的ALTER说法是正确的：
ALTER TABLE `gamerooms`
ADD COLUMN `id_public` varchar(36) NOT NULL DEFAULT 'something' AFTER `id`
According to my MySQL Pocket Reference, if you don't provide a default value for a column that is defined as NOT NULL:
根据我的 MySQL Pocket Reference，如果您没有为定义为 NOT NULL 的列提供默认值：
MySQL picks a value based on the type of the field
MySQL根据字段的类型选择一个值
In this case, I'm guessing the default would be empty string. Once your column has been added, simply create a new index for the column, and rebuild the index using a null alterationinstruction like so:
在这种情况下，我猜默认值是空字符串。添加列后，只需为该列创建一个新索引，然后使用空更改指令重建索引，如下所示：
CREATE INDEX myIndex ON gamerooms(id_public);
ALTER TABLE gamerooms ENGINE = InnoDB;
You may be able to create the index at the same time you do the insert. My MySQL-fu isn't strong enough to know how to do that.
您可以在执行插入操作的同时创建索引。我的 MySQL-fu 不够强大，无法知道如何做到这一点。
回答by nolt2232
Should the existing records have a value once you create this new column? If yes, you could do this in multiple steps. First, create the new column without constraint or index and then back populate it with the UUID for all existing records. Once everything is populated, add the not null constraint and your indexes.
创建此新列后，现有记录是否应该具有值？如果是，您可以分多个步骤执行此操作。首先，创建没有约束或索引的新列，然后使用所有现有记录的 UUID 重新填充它。填充完所有内容后，添加非空约束和索引。
回答by xdazz
As a UUID is a 128-bit number, you don't need a varcharcolumn to store it. a char(16)column would just be ok for saving a UUID binary data.
由于 UUID 是 128 位数字，因此您不需要varchar列来存储它。一char(16)列可以用来保存 UUID 二进制数据。
ALTER TABLE `gamerooms` ADD COLUMN `id_public` char(16) NOT NULL DEFAULT '' AFTER `id`
