MySQL 在表中添加新列，其值取决于同一表中另一列的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14500911/
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
add new column in table with value depending value of another column in same table
提问by nohan
I am new in Mysql . I want to add a column in table with value depending another column value in the same table. For example I have company_table as below :
我是 Mysql 新手。我想在表中添加一列，其值取决于同一表中的另一列值。例如我有 company_table 如下：
fldId | companyName | date
--------------------------------
1 | adeco | 2012-01-12
2 | mic | 2001-03-09
3 | manpower | Null
4 | linar | Null
5 | dlank | 1999-02-28
I want to add 'fldState' column in this table depending on value of 'date' column. For example if value of 'date' column is not Null then value of 'fldState' should be 1 and if it's Null then value of 'fldState' should be 2. like below
我想根据“日期”列的值在此表中添加“fldState”列。例如，如果 'date' 列的值不为 Null，则 'fldState' 的值应为 1，如果为 Null，则 'fldState' 的值应为 2。如下所示
fldId | companyName | date | fldState
--------------------------------------------
1 | adeco | 2012-01-12 | 1
2 | mic | 2001-03-09 | 1
3 | manpower | Null | 2
4 | linar | Null | 2
5 | dlank | 1999-02-28 | 1
回答by John Woo
From your existing table, you need to create an ALTERstatement so that you can add new column.
从您现有的表中，您需要创建一个ALTER语句，以便您可以添加新列。
ALTER TABLE mytableName ADD fldState INT;
After the statement has been successfully executed, you can now update all the records,
语句成功执行后，您现在可以更新所有记录，
UPDATE myTableName
SET fldState = IF(date IS NULL, 2, 1)
回答by Md. Maruf Hossain
To add new new column you can use following command
要添加新列，您可以使用以下命令
ALTER TABLE company_table ADD fldState;
If you want to add those column in your wishing place . As like if you want to add fldState after companyName , Then use as like following
如果您想将这些列添加到您希望的位置。就像你想在 companyName 之后添加 fldState 一样，然后使用如下
ALTER TABLE company_table ADD fldState AFTER companyName;
IF you want to add Column as First Column, Then use aslike following
如果要将列添加为第一列，则使用如下所示
ALTER TABLE company_table ADD fldState FIRST;
if you don't use any thing more , Then as default it will be placed at last.
如果你不再使用任何东西，那么默认情况下它会放在最后。
Now use as like following command to Copy Column data.
现在使用如下命令复制列数据。
UPDATE company_table SET fldState = IF(date IS NULL, 2, 1);
回答by Bogdan Gavril MSFT
Are you sure this is what you want to do - add a new column? You are violating the 3rd normal form, 3NF by introducing a column that only depends on the date.
你确定这是你想要做的 - 添加一个新列？通过引入仅取决于日期的列，您违反了第三范式 3NF。
You can read more about it on wikipedia. Basically all your columns should be dependent on the determinant of the table (the PK).
您可以在wikipedia上阅读更多相关信息。基本上你所有的列都应该依赖于表的行列式（PK）。
So what you can consider doing is creating that column only when you do the select:
因此，您可以考虑做的是仅在执行选择时创建该列：
SELECT .. fldState = (process date)
FROM ...
This would be better unless you plan to join on that column a lot, in which case speed beats the 3NF :)
这会更好，除非您计划经常加入该专栏，在这种情况下速度超过 3NF :)
