Unknown column 't_user.id' in 'where clause'（通过字段名删除不了数据）

最新推荐文章于 2025-06-12 14:05:20 发布

转载最新推荐文章于 2025-06-12 14:05:20 发布 · 540 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/package-java/p/10494380.html

文章标签：

#数据库

本文详细解析了在使用MySQL数据库时，遇到的因字段名包含空格导致的DELETE语句无法正常工作的问题。通过正确的语法调整，解决了字段名带空格时的数据删除难题。

创建员工信息表t_user

CREATE TABLE t_user(

　　id INT PRIMARY KEY AUTO_INCREMENT,

　　username VARCHAR(20) ,

　　password VARCHAR(20) ,

　　age int ,

　　phone VARCHAR(20) ,

　　email VARCHAR(20) ,

　　is_Delete int

)DEFAULT CHARSET=UTF8;

往员工表中插入数据：

INSERT INTO t_user VALUES(null,'张三','123456',23,'110','11111@qq.com',1),(null,'历史','123456',23,'110','11111@qq.com',1),(null,'孙悟空','123456',23,'110','11111@qq.com',2),(null,'李白','123456',23,'110','11111@qq.com',2),(null,'八戒','123456',23,'110','11111@qq.com',2);

问题：发现用delete from t_user where id=6;这个语句删除不了,其它字段名也试过了也不行，如下:

mysql> select * from t_user;
+----------+----------------+----------------+-----------+-------------+--------------+-----------------+
| 　　id   | 　　username   | 　　password   | 　　age   | 　　phone   | 　　email    | 　　is_Delete   |
+----------+----------------+----------------+-----------+-------------+--------------+-----------------+
|        1 | 唐僧           | 123456         |        23 | 110         | 11111@qq.com |               2 |
|        2 | 张三           | 123456         |        23 | 110         | 11111@qq.com |               1 |
|        3 | 历史           | 123456         |        23 | 110         | 11111@qq.com |               1 |
|        4 | 孙悟空         | 123456         |        23 | 110         | 11111@qq.com |               2 |
|        5 | 李白           | 123456         |        23 | 110         | 11111@qq.com |               2 |
|        6 | 八戒           | 123456         |        23 | 110         | 11111@qq.com |               2 |
+----------+----------------+----------------+-----------+-------------+--------------+-----------------+
6 rows in set (0.00 sec)

mysql> mysql> delete from t_user where id=6;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delete from t_user where id=6' at line 1
mysql> delete from t_user where id='6';
ERROR 1054 (42S22): Unknown column 'id' in 'where clause'
mysql> delete form t_user where id=6;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't_user where id=6' at line 1
mysql> delete from t_user where id=6;
ERROR 1054 (42S22): Unknown column 'id' in 'where clause'
mysql> delete from t_user where username='八戒';
ERROR 1054 (42S22): Unknown column 'username' in 'where clause'
mysql> delete from t_user where t_user.id=6;
ERROR 1054 (42S22): Unknown column 't_user.id' in 'where clause'
mysql>

然后发现是表中的字段名有空格，正常的没空格的如下：

mysql> select * from dept;
+----+--------+
| id | name   |
+----+--------+
|  1 | 神仙   |
|  2 | 妖怪   |
+----+--------+

字段名出现空格的话，通过字段名进行数据操作的时候要给字段名添加单引号，如下：

mysql> delete from t_user where 'id'=6;
Query OK, 0 rows affected, 1 warning (0.07 sec)

转载于:https://www.cnblogs.com/package-java/p/10494380.html