Window’s Note
记录自己在实际操作中用到的问题以及最后的解决方案
前提描述
注册页面用户填写信息后需要执行insert()
方法向数据库中插入新用户的信息,需要插入的用户信息变量在数据库中定义如下:
* mail varchar(255)
* pwd varchar(255)
* isManager bit(1)
* isNormal bit(1)
varchar类型直接对应java中String,bit类型对应java中boolean类型
问题
java中执行插入新用户的语句如下
String sql = "INSERT INTO household (mail,pwd,isManager,isNormal) VALUES ('" + mail + "','" + pwd + "'," + false + "," + true + ")";
编译运行报错
Unknown column 'isManger' in 'field list'
尝试修改代码如下依旧报错如上
String sql = "INSERT INTO household (mail,pwd,isManager,isNormal) VALUES ('" + mail + "','" + pwd + "','0'," + true + ")";
String sql = "INSERT INTO household (mail,pwd,isManager,isNormal) VALUES ('" + mail + "','" + pwd + "',0,1)" ;
String sql = "INSERT INTO household (mail, pwd, isManger, isNormal) VALUES ('" + mail + "','" + pwd + "'," + 0 + "," + 1 + ")";
解决问题思路梳理
- 报错的原因是因为上述几种方式java中变量sql中的isManager的value并不是真正sql可识别的bit类型
- 首先需要确定SQL中的布尔类型是不是bit
- 查阅发现MySQL中并不支持布尔类型, MySQL中tinyint(1)是变相的布尔类型, 默认值1, 0 分别对应true, false
- 修改数据库中isManager和isNormal的类型为tinyint(1)
- 执行上述几类代码后依旧报相同的错误
- 首先需要确定SQL中的布尔类型是不是bit
乌龙一场
我把sql中isManager写成了isManger, 大家还是要养成细心看报错的习惯啊
后续(值得小白一看)
-
把SQL中isManager和isNormal的数值类型修改为tinyint(1)后,sql中赋值’0’, 0和false都可以成功插入数据,且结果一致,具体java代码和数据库中如下:
String sql0 = "INSERT INTO household (mail,pwd,isManager,isNormal) VALUES ('testUser1','test',0,1)" ;
String sql0 = "INSERT INTO household (mail,pwd,isManager,isNormal) VALUES ('testUser2','test'," + 0 + "," + 1 + ")";
String sql0 = "INSERT INTO household (mail,pwd,isManager,isNormal) VALUES ('testUser3','test','0','1')" ;
String sql0 = "INSERT INTO household (mail,pwd,isManager,isNormal) VALUES ('testUser4','test',false,true)" ;
String sql0 = "INSERT INTO household (mail,pwd,isManager,isNormal) VALUES ('testUser5','test'," + false + "," + true + ")";
数据库中结果如下
id mail pwd isManager isNormal 1 testUser1 test 0 1 2 testUser2 test 0 1 3 testUser3 test 0 1 4 testUser4 test 0 1 5 testUser5 test 0 1 注意执行以下代码会报错,因为传入的类型变为了varchar
String sql0 = "INSERT INTO household (mail,pwd,isManager,isNormal) VALUES ('testUser4','test','false','true')" ;