MySQL用NULL更新Varchar列 [英] MySQL updating Varchar Column with NULL

问题描述

在 mysql 中，我需要使用空值更新 Varchar 列.我可以使用这样的查询:

In mysql I need to update a Varchar column with a null value. I can use some query like this :

update books set Volume='NULL' WHERE BookId=5457 

但它只是用 NULL 作为字符串更新列如果我在 mysql 函数上使用此列数据 COALESCE 它返回 NULL 作为字符串而不是真正的 NULL.我的主要问题在于这个声明:

but it just updates the column with NULL as string If I use this column data on mysql function COALESCE it returns the NULL as string not as real NULL. My main problem is in this statement:

Select Title,COALESCE(Volume,PackId) From books where BookId=5457 

我需要 COALESCE 来获取第一个 not null，如果我使用 qoutes 运行更新，它不会添加真正的 NULL> 到 mysql 行和 COALESCE 不能正常工作并返回 Volume not PackId

I need the COALESCE to get the first not null and if I run my update with qoutes or not, it does not add real NULL to mysql row and COALESCE does not work properly and returns Volume not PackId

是否有特殊的语法可以做到这一点?

Is there a special syntax to do this?

推荐答案

我找到了答案.Mysql中NULL和empty是有区别的.NULL 表示未设置，空表示 0；

I found the answer. There is difference between NULL and empty in Mysql. NULL means not set and empty means 0;

对于我在创建表或更改表以添加 Varchar 类型的新字段时的问题，我应该允许 Mysql 将 Null 值设置为这样的字段:

For my question when I create the table or alter it for adding new fields with Varchar type, I should permit Mysql to could set Null value to the field like this:

ALTER TABLE `books` ADD `Volume` VARCHAR(25) NULL

或禁止 Mysql 之类的:

or prohibit Mysql like:

ALTER TABLE `books` ADD `Volume` VARCHAR(25) NOT NULL

正在运行

update books set Volume=NULL WHERE BookId=5457

会导致设置真正的NULL，COALESCE函数会将其理解为NULL.

will lead to setting real NULL that COALESCE function will understand it as NULL.

感谢大家的帮助.

这篇关于MySQL用NULL更新Varchar列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！