对于mysql的warnings如何处理？ Field 'userName' doesn't have a default value

cnxiao

我先创建一个表，然后分别用mysql客户端 和 一段java程序插入一条记录
1， 如果是mysql客户端， 有warnings产生，但成功插入了
2， 如果是java程序的话有异常抛出，不能插入， 是connector 的问题吗？

下面是建表的SQL:
CREATE TABLE  `xudc`.`userCore` (
  `userId` mediumint(9) unsigned NOT NULL,
  `userName` varchar(14) NOT NULL,
  `passwordHash` varchar(20) NOT NULL,
  `email` text NOT NULL,
  `emailVisible` tinyint(1) NOT NULL,
  `safetyQuestion` varchar(50) NOT NULL,
  `safetyAnswer` varchar(50) NOT NULL,
  `registerDatetime` bigint(20) NOT NULL,
  `loginCount` int(11) NOT NULL,
  `lastLoginDatetime` bigint(20) NOT NULL,
  `lastLoginIp` varchar(15) NOT NULL,
  `isTerminaled` tinyint(1) NOT NULL,
  `updateLogs` text NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='the table store user core data'

下面是mysql客户端的插入：
mysql> insert into userCore(userId)values(2342342);
Query OK, 1 row affected, 12 warnings (0.00 sec)

mysql> show warnings;
+---------+------+--------------------------------------------------------+
| Level   | Code | Message                                                |
+---------+------+--------------------------------------------------------+
| Warning | 1364 | Field 'userName' doesn't have a default value          |
| Warning | 1364 | Field 'passwordHash' doesn't have a default value      |
| Warning | 1364 | Field 'email' doesn't have a default value             |
| Warning | 1364 | Field 'emailVisible' doesn't have a default value      |
| Warning | 1364 | Field 'safetyQuestion' doesn't have a default value    |
| Warning | 1364 | Field 'safetyAnswer' doesn't have a default value      |
| Warning | 1364 | Field 'registerDatetime' doesn't have a default value  |
| Warning | 1364 | Field 'loginCount' doesn't have a default value        |
| Warning | 1364 | Field 'lastLoginDatetime' doesn't have a default value |
| Warning | 1364 | Field 'lastLoginIp' doesn't have a default value       |
| Warning | 1364 | Field 'isTerminaled' doesn't have a default value      |
| Warning | 1364 | Field 'updateLogs' doesn't have a default value        |
+---------+------+--------------------------------------------------------+

下面是java程序的插入和异常抛出：
Connection conn = ConnectionManager.getConnection(DatabaseConfig.DEFAULT_CONNECTION_PROVIDER_USER);
String sql = "INSERT INTO userCore (userId)VALUES(?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setLong(1, 2332423432l);
pstmt.executeUpdate();

java.sql.SQLException: Field 'userName' doesn't have a default value
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:3256)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1313)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1585)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1500)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1485)
        at com.jeadu.platform.db.TestCP.main(TestCP.java:66)

yejr

修改表结构的字段定义，加上默认值不就ok了？

cnxiao

谢谢， 但问题是某些field不允许有默认值， 像email，它的类型设为text, 不能有默认值！ 而java程序段中，有抛出异常，无法执行插入？？？

我觉的这可能是java-connector的一个bug!!!

java.sql.SQLException: Field 'email' doesn't have a default value
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:3256)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1313)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1585)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1500)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1485)
        at com.jeadu.platform.db.TestCP.main(TestCP.java:66)

wildlily980

你insert的时候把那些不能默认的全部写上''

cnxiao

多谢，解决了， 建表时，NOT NULL 类型的都给出默认值，其他类型的默认值为NULL

但我还是想不通为何相同的java代码， 4.x的版本可以而5.x的版本就不可以！！！

yejr

谁说 text 类型就不能有默认值了，你的mysql版本多少？

cnxiao

bin/mysqladmin  Ver 8.41 Distrib 5.0.45, for pc-linux-gnu on i686
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version          5.0.45
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /tmp/mysql.sock
Uptime:                 29 sec

Threads: 1  Questions: 1  Slow queries: 0  Opens: 12  Flush tables: 1  Open tables: 6  Queries per second avg: 0.034

默认值和mode或多或少有些关系！ MySQL manual 上说：“Prior to MySQL 5.0.2, .............................BLOB and TEXT columns cannot be assigned a default value. ”

我或许找到先前的原因了, java-connector中对数据库的默认模式是strict mode, 某个不符合5.0.2以后版本规范的语句，在还没有被数据库执行以前，就被java-connector 抛出异常了， 这是我猜的。 下面是MySQL manual的摘抄, ：

http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html

As of MySQL 5.0.2, if a column definition includes no explicit DEFAULT value, MySQL determines the default value as follows:

If the column can take NULL as a value, the column is defined with an explicit DEFAULT NULL clause. This is the same as before 5.0.2.

If the column cannot take NULL as the value, MySQL defines the column with no explicit DEFAULT clause. For data entry, if an INSERT or REPLACE statement includes no value for the column, MySQL handles the column according to the SQL mode in effect at the time:

If strict SQL mode is not enabled, MySQL sets the column to the implicit default value for the column data type.

If strict mode is enabled, an error occurs for transactional tables and the statement is rolled back. For non-transactional tables, an error occurs, but if this happens for the second or subsequent row of a multiple-row statement, the preceding rows will have been inserted.

[ 本帖最后由 cnxiao 于 2007-8-18 12:07 编辑 ]

meilixinqinggmq

text怎么给默认值？不通过代码的话回复 6# yejr