MySQL 如何使用空值更新列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3870540/
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 update column with null value
提问by jim
I am using mysql and need to update a column with a null value. I have tried this many different ways and the best I have gotten is an empty string.
我正在使用 mysql 并且需要使用空值更新列。我尝试了很多不同的方法，我得到的最好的方法是空字符串。
Is there a special syntax to do this?
是否有特殊的语法来做到这一点？
回答by Daniel Vassallo
No special syntax:
没有特殊语法：
CREATE TABLE your_table (some_id int, your_column varchar(100));
INSERT INTO your_table VALUES (1, 'Hello');
UPDATE your_table
SET your_column = NULL
WHERE some_id = 1;
SELECT * FROM your_table WHERE your_column IS NULL;
+---------+-------------+
| some_id | your_column |
+---------+-------------+
| 1 | NULL |
+---------+-------------+
1 row in set (0.00 sec)
回答by Gumbo
NULLis a special value in SQL.So to null a property, do this:
NULL是 SQL 中的特殊值。因此，要将属性设为 null，请执行以下操作：
UPDATE table SET column = NULL;
回答by korubilli krishna chaitanya
Use ISinstead of =This will solve your problem
example syntax:
使用IS而不是=这将解决您的问题示例语法：
UPDATE studentdetails
SET contactnumber = 9098979690
WHERE contactnumber IS NULL;
回答by Thiago Mata
Remember to look if your column can be null. You can do that using
请记住查看您的列是否可以为空。你可以使用
mysql> desc my_table;
If your column cannot be null, when you set the value to null it will be the cast value to it.
如果您的列不能为空，则当您将该值设置为空时，它将是它的强制转换值。
Here a example
这里有一个例子
mysql> create table example ( age int not null, name varchar(100) not null );
mysql> insert into example values ( null, "without num" ), ( 2 , null );
mysql> select * from example;
+-----+-------------+
| age | name |
+-----+-------------+
| 0 | without num |
| 2 | |
+-----+-------------+
2 rows in set (0.00 sec)
mysql> select * from example where age is null or name is null;
Empty set (0.00 sec)
回答by Daniel Dunn
For those facing a similar issue, I found that when 'simulating' a SET = NULLquery, PHPMyAdmin would throw an error. It's a red herring.. just run the query and all will be well.
对于那些面临类似问题的人，我发现在“模拟”SET = NULL查询时，PHPMyAdmin 会抛出错误。这是一个红鲱鱼.. 只需运行查询，一切都会好起来的。
回答by Nitish Kumar Pal
In the above answers, many ways and repetitions have been suggested for the same. I kept looking for an answer as mentioned is the question but couldn't find here.
在上面的答案中，已经提出了许多相同的方法和重复。我一直在寻找问题的答案，但在这里找不到。
But opposite of the above question "update a column with a null value" Could be "UPDATE ALL THE ROWS IN THE COLUMN TO NULL"
但与上述问题“更新具有空值的列”相反可能是“将列中的所有行都更新为空”
In such a situation following works
在这种情况下，以下作品
update table_name
set field_name = NULL
where field_name is not NULL;
isas well is notworks in mysql
is也is not适用于 mysql
回答by mayank nigam
If you want to set null value using update query set column value to NULL (without quotes) update tablename set columnname = NULL
如果要使用更新查询将列值设置为 NULL（不带引号）来设置空值，请更新表名设置列名 = NULL
However, if you are directly editing field value inside mysql workbench then use (Esc + del) keystroke to insert null value into selected column
但是，如果您直接在 mysql 工作台中编辑字段值，则使用 (Esc + del) 按键将空值插入选定列
回答by Cyber Gangster
use isinstead of =
使用is代替=
Eg: Select * from table_name where column is null
例如： Select * from table_name where column is null
回答by ReverseEMF
Another possible reason for the empty string, rather than a truenull is that the field is an index or is part of an index. This happened to me: using phpMyAdmin, I edited the structure of a field in one of my tables to allow NULLsby checking the "Null" checkbox then hitting the "Save" button. "Table pricing has been altered successfully" was displayed so I assumed that the change happened -- it didn't. After doing an UPDATEto set all of those fields to NULL, they were, instead, set to empty strings, so I took a look at the table structure again and saw that the "Null" column for that field was set to 'no'. That's when I realized that the field was part of the Primary key!
空字符串而不是真正的null 的另一个可能原因是该字段是索引或索引的一部分。这发生在我身上：使用 phpMyAdmin，我通过选中“ Null”复选框然后点击“ Save”按钮来编辑我的一个表中的字段结构以允许NULL。显示“表定价已成功更改”，因此我认为更改发生了 - 事实并非如此。在执行UPDATE以将所有这些字段设置为NULL 后，它们被设置为空字符串，因此我再次查看了表结构并看到“ Null” 该字段的列设置为“否”。那时我意识到该字段是主键的一部分！
回答by ReverseEMF
if you follow
如果你遵循
UPDATE table SET name = NULL
then name is "" not NULL IN MYSQL means your query
那么 name is "" not NULL IN MYSQL 表示您的查询
SELECT * FROM table WHERE name = NULLnot work or disappoint yourself
SELECT * FROM table WHERE name = NULL不工作或让自己失望
