SQL 只返回具有空字段的行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4651717/
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
Only return rows that have an empty field?
提问by Mik0r
I have a table where one of the fields can be empty. I'm trying to return only these rows where this field is empty. But I keep getting errors just doing WHERE field = "" ... WHERE field = '' ... WHERE field = null
我有一张表，其中一个字段可以为空。我试图仅返回此字段为空的这些行。但是我一直在做 WHERE field = "" ... WHERE field = '' ... WHERE field = null 时出错
Any ideas what I'm missing?
任何想法我错过了什么？
回答by Larry Lustig
In SQL, a completely empty field is said to be NULL. For NULL searches you do not use equals (=), but rather the special operator IS NULL:
在 SQL 中，一个完全空的字段被称为 NULL。对于 NULL 搜索，您不使用等于 (=)，而是使用特殊运算符 IS NULL：
SELECT * FROM table WHERE field IS NULL
Access allows you to have not NULL empty fields if you allow empty strings, this is a bad idea as it makes it difficult to distinguish visual between a NULL value and a 0-length string, so I suggest you don't permit it in your database.
如果您允许空字符串，Access 允许您没有 NULL 空字段，这是一个坏主意，因为它很难区分 NULL 值和 0 长度字符串，所以我建议您不要在您的数据库。
回答by onedaywhen
SELECT *
FROM MyTable
WHERE IIF(MyField = ' ', NULL, MyField) IS NULL;
UPDATE: here's a demonstration of how "ANSI padding" works in the Access Database Engine (ACE, Jet, whatever), which somehow seems to be necessary (surely every SQL product in the land works this way...?): just paste into any VBA (Access, Excel, Word, etc) or VB6 module and run (no references etc required): if it is true that a single space is equal to a zero-length string (ZLS) or an 'undetermined' number of spaces then you will see a list of Ys:
更新：这里演示了“ANSI 填充”如何在 Access 数据库引擎（ACE、Jet 等）中工作，这在某种程度上似乎是必要的（当然，陆地上的每个 SQL 产品都以这种方式工作......？）：只需粘贴进入任何 VBA（Access、Excel、Word 等）或 VB6 模块并运行（不需要引用等）：如果单个空格等于零长度字符串 (ZLS) 或“不确定”数量的空格然后你会看到一个Ys列表：
Sub Fundamentals()
On Error Resume Next
Kill Environ$("temp") & "\DropMe.mdb"
On Error GoTo 0
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"
With .ActiveConnection
Dim SQL As String
SQL = _
"SELECT IIF(SPACE(0) = SPACE(1), 'Y', 'N')," & vbCr & _
" IIF(SPACE(1) = SPACE(1), 'Y', 'N')," & vbCr & _
" IIF(SPACE(2) = SPACE(1), 'Y', 'N')," & vbCr & _
" IIF(SPACE(3) = SPACE(1), 'Y', 'N')," & vbCr & _
" IIF(SPACE(4) = SPACE(1), 'Y', 'N')," & vbCr & _
" IIF(SPACE(5) = SPACE(1), 'Y', 'N')," & vbCr & _
" IIF(SPACE(55) = SPACE(1), 'Y', 'N')," & vbCr & _
" IIF(SPACE(99) = SPACE(1), 'Y', 'N')," & vbCr & _
" IIF(SPACE(255) = SPACE(1), 'Y', 'N')," & vbCr & _
" IIF(SPACE(4321) = SPACE(1), 'Y', 'N')," & vbCr & _
" IIF(SPACE(54321) = SPACE(1), 'Y', 'N')," & vbCr & _
" IIF(SPACE(654321) = SPACE(1), 'Y', 'N');"
.Execute SQL
Dim rs
Set rs = .Execute(SQL)
MsgBox rs.GetString(, , vbCr)
End With
Set .ActiveConnection = Nothing
End With
End Sub
UPDATE2:
更新2：
Certainly Jet/ACE doesn't pad fields to fixed length!
当然，Jet/ACE 不会将字段填充到固定长度！
Incorrect. The Access Database has a fixed width text data type generally known as NCHAR(n)(though other synonym apply) that does indeed pad column values to fixed length...
不正确。Access 数据库具有固定宽度的文本数据类型，通常称为NCHAR(n)（尽管其他同义词适用）确实将列值填充到固定长度...
What data type is NCHAR(10) in the Access table designer?
Access 表设计器中的 NCHAR(10) 是什么数据类型？
I don't it will show in the table designer thing correctly. The Access UI still lags behind the Jet 4.0 technology, there are many such omissions. I don't have Access installed at the moment -- perhaps someone could run the following code, open the .mdb in the Access UI and tell us...?
我不知道它会正确显示在表格设计器中。Access UI 仍然落后于 Jet 4.0 技术，这样的疏漏还很多。我目前没有安装 Access——也许有人可以运行以下代码，在 Access UI 中打开 .mdb 并告诉我们......？
Sub AccessNChar()
On Error Resume Next
Kill Environ$("temp") & "\DropMe.mdb"
On Error GoTo 0
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"
With .ActiveConnection
Dim Sql As String
Sql = "CREATE TABLE TestNChar (col1 NCHAR(10));"
.Execute Sql
Sql = "INSERT INTO TestNChar (col1) VALUES (SPACE(1));"
.Execute Sql
Sql = "SELECT LEN(col1) FROM TestNChar;"
Dim rs
Set rs = .Execute(Sql)
MsgBox rs.GetString
End With
Set .ActiveConnection = Nothing
End With
End Sub
回答by Stephen Wrighton
What do you mean by errors?
你说的错误是什么意思？
But, if you want to get just those rows where the fields are empty, attempt something like thus:
但是，如果您只想获取字段为空的行，请尝试如下操作：
SELECT * FROM MyTable WHERE LTRIM(RTRIM(ISNULL(MyField, ''))) = ''
回答by Parris Varney
Does your query have three WHERE clauses? If so, change the second two to OR
您的查询是否有三个 WHERE 子句？如果是这样，将后两个更改为 OR
回答by Deepak N
Yeah, I was facing the same But finally I tried the following mysql query it worked and saved me.
是的，我面临着同样的问题，但最后我尝试了以下 mysql 查询，它起作用并救了我。
SELECT * from your_table_name WHERE field_name IS NULL;
Go ahead with this query it will select only the rows which are empty.
继续这个查询，它将只选择空行。
