mysql - 从表中删除空值行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22006909/
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
mysql - Removing null value rows from table
提问by Achilles
I have a table "user"with over 60 columns. One of the column's name is "username"
我有一个包含 60 多列的“用户”表。列的名称之一是“用户名”
I want to remove the rows where the usernamefield is empty or NULL
我想删除用户名字段为空或NULL 的行
How can I do this?
我怎样才能做到这一点？
Thank you!
谢谢！
回答by Nagaraj S
Try this
尝试这个
DELETE FROM user WHERE username IS NULL;
or
或者
DELETE FROM user WHERE username = '';
回答by Deepak
If you want to delete all those rows containing username = NULL AND where username is empty string ("") as well
如果您想删除所有包含 username = NULL AND 其中 username 为空字符串 ("") 的行
then
然后
DELETE FROM table_name WHERE username IS NULL OR username = '';
It is advised to first do a SELECTquery with same WHEREcondition as that you are going to use in DELETE query to see which rows will be deleted:
建议首先SELECT使用与WHERE您将在 DELETE 查询中使用的条件相同的条件进行查询，以查看将删除哪些行：
SELECT * FROM table_name WHERE username IS NULL OR username = "";
回答by Akila Viduranga Liyanaarachchi
Here I have created a script for any kind of SQL table. please copy this stored procedure and create this on your Environment and run this stored procedure with your Table.
在这里，我为任何类型的 SQL 表创建了一个脚本。请复制此存储过程并在您的环境中创建它并使用您的表运行此存储过程。
exec [dbo].[SP_RemoveNullValues] 'Your_Table_Name'
stored procedure
存储过程
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--akila liyanaarachchi
Create procedure [dbo].[SP_RemoveNullValues](@PTableName Varchar(50) ) as
begin
DECLARE Cussor CURSOR FOR
SELECT COLUMN_NAME,TABLE_NAME,DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @PTableName
OPEN Cussor;
Declare @ColumnName Varchar(50)
Declare @TableName Varchar(50)
Declare @DataType Varchar(50)
Declare @Flage int
FETCH NEXT FROM Cussor INTO @ColumnName,@TableName,@DataType
WHILE @@FETCH_STATUS = 0
BEGIN
set @Flage=0
If(@DataType in('bigint','numeric','bit','smallint','decimal','smallmoney','int','tinyint','money','float','real'))
begin
set @Flage=1
end
If(@DataType in('date','atetimeoffset','datetime2','smalldatetime','datetime','time'))
begin
set @Flage=2
end
If(@DataType in('char','varchar','text','nchar','nvarchar','ntext'))
begin
set @Flage=3
end
If(@DataType in('binary','varbinary'))
begin
set @Flage=4
end
DECLARE @SQL VARCHAR(MAX)
if (@Flage in(1,4))
begin
SET @SQL =' update ['+@TableName+'] set ['+@ColumnName+']=0 where ['+@ColumnName+'] is null'
end
if (@Flage =3)
begin
SET @SQL =' update ['+@TableName+'] set ['+@ColumnName+'] = '''' where ['+@ColumnName+'] is null '
end
if (@Flage =2)
begin
SET @SQL =' update ['+@TableName+'] set ['+@ColumnName+'] ='+'''1901-01-01 00:00:00.000'''+' where ['+@ColumnName+'] is null '
end
EXEC(@SQL)
FETCH NEXT FROM Cussor INTO @ColumnName,@TableName,@DataType
END
CLOSE Cussor
DEALLOCATE Cussor
END
