Mysql数据类型TINYINT(1)与BOOLEAN踩坑记

  熟悉Mysql的同学应该都知道,Mysql查询的boolean结果将输出为0或者1.

  比如:

select 1=1;

  其输出结果为1。

  查阅mysql官方文档仅找到如下描述:

11.10 Using Data Types from Other Database Engines

To facilitate the use of code written for SQL implementations from other vendors, MySQL maps data types as shown in the following table. These mappings make it easier to import table definitions from other database systems into MySQL.

Other Vendor TypeMySQL Type
BOOL TINYINT
BOOLEAN TINYINT
CHARACTER VARYING(M) VARCHAR(M)
FIXED DECIMAL
FLOAT4 FLOAT
FLOAT8 DOUBLE
INT1 TINYINT
INT2 SMALLINT
INT3 MEDIUMINT
INT4 INT
INT8 BIGINT
LONG VARBINARY MEDIUMBLOB
LONG VARCHAR MEDIUMTEXT
LONG MEDIUMTEXT
MIDDLEINT MEDIUMINT
NUMERIC DECIMAL
Other Vendor TypeMySQL Type

Data type mapping occurs at table creation time, after which the original type specifications are discarded. If you create a table with types used by other vendors and then issue a DESCRIBE tbl_name statement, MySQL reports the table structure using the equivalent MySQL types. For example:

 
mysql> CREATE TABLE t (a BOOL, b FLOAT8, c LONG VARCHAR, d NUMERIC);
Query OK, 0 rows affected (0.00 sec)

mysql> DESCRIBE t;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| a     | tinyint(1)    | YES  |     | NULL    |       |
| b     | double        | YES  |     | NULL    |       |
| c     | mediumtext    | YES  |     | NULL    |       |
| d     | decimal(10,0) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

  我想说的是,今天使用一套中间件对kafka消息进行解析为mysql 语句,其中遇到如下的问题,

  1. 目标表有一字段设置类型为:tinyint(1)。
  2. 源表同步消息中接收到相同类型的数据。
  3. 其中中间件中有如下解析部分:
    public void setStatement(PreparedStatement statement, DatabaseType databaseType, boolean timestampChangeToLong) throws SQLException {
            if (this.value == null) {
                statement.setNull(this.index, this.sqlType);
            } else {
                switch(this.sqlType) {
                case -15:
                case -9:
                case 1:
                case 12:
                case 2005:
                    String strVal = String.valueOf(this.value);
                    statement.setString(this.index, strVal);
                    break;
                case -7:
                case 16:
                    boolean booleanVal = (Boolean)this.value; //tinyint(1) 类型的表设计字段直接进入该case,由于接收到的消息中的数据为0或者1,直接在该位置报类转换异常。
                    statement.setBoolean(this.index, booleanVal);
                    break;
                case -6:
                    int val2 = (Integer)this.value;
                    statement.setInt(this.index, val2);
                    break;
                case -5:
                    long longVal = (Long)this.value;
                    statement.setLong(this.index, longVal);
                    break;
                case 2:
                    this.setStatementDataTypeNumeric(statement);
                    break;
                case 3:
                    this.setStatementDataTypeDecimal(statement, databaseType, timestampChangeToLong);
                    break;
                case 4:
                    int val = (Integer)this.value;
                    statement.setInt(this.index, val);
                    break;
                case 5:
                    int val1 = (Integer)this.value;
                    statement.setInt(this.index, val1);
                    break;
                case 6:
                    float floatVal = (Float)this.value;
                    statement.setFloat(this.index, floatVal);
                    break;
                case 8:
                    double doubelVal = (Double)this.value;
                    statement.setDouble(this.index, doubelVal);
                    break;
                case 91:
                    this.setStatementDataTypeDate(statement, databaseType);
                    break;
                case 92:
                    Date timeVal = (Date)this.value;
                    Time sqlTime = new Time(timeVal.getTime());
                    statement.setTime(this.index, sqlTime);
                    break;
                case 93:
                    this.setStatementDataTypeTimestamp(statement, timestampChangeToLong);
                    break;
                default:
                    throw new ConsumeException("sqlType " + this.sqlType + " is not support");
                }
    
            }
        }

     

  4. 怎样获取的数字类型呢,代码如下:
    protected Database loadInternal(String database) {
            Connection connection = null;
    
            Database var28;
            try {
                connection = this.dataSource.getConnection();//获取连接
                DatabaseMetaData metaData = connection.getMetaData();//获取元数据
                String catalog = null;
                String[] tableTypes = new String[]{"TABLE"};
                String databasePattern = this.databaseSchema != null ? this.databaseSchema : database;
                ResultSet tablesResultSet = metaData.getTables((String)catalog, databasePattern, "%", tableTypes);
                Database db = new Database();
                db.setName(database);
    
                Table tablei;
                while(tablesResultSet.next()) {
                    String tableName = tablesResultSet.getString("TABLE_NAME");
                    tablei = new Table(tableName);
                    db.addTable(tablei);
                }
    
                Iterator var27 = db.getTables().iterator();
    
                while(var27.hasNext()) {
                    tablei = (Table)var27.next();
                    ResultSet columnsResultSet = metaData.getColumns((String)catalog, databasePattern, tablei.getName(), (String)null);
    
                    while(columnsResultSet.next()) {
                        String columnName = columnsResultSet.getString("COLUMN_NAME");
                        int sqlType = columnsResultSet.getInt("DATA_TYPE");//此处拿到mysql返回的字段类型
                        String typeName = columnsResultSet.getString("TYPE_NAME");
                        int size = columnsResultSet.getInt("COLUMN_SIZE");
                        boolean nullable = 1 == columnsResultSet.getInt("NULLABLE");
                        Column column = new Column();
                        column.setName(columnName);
                        column.setNullable(nullable);
                        column.setSqlType(sqlType);
                        column.setTypeName(typeName);
                        column.setSize(size);
                        tablei.addColumn(column);
                    }
                }
    
                var28 = db;
            } catch (Exception var25) {
                throw new RuntimeException("load schema exception", var25);
            } finally {
                if (connection != null) {
                    try {
                        connection.close();
                    } catch (SQLException var24) {
                        ;
                    }
                }
    
            }
    
            return var28;
        }

     

  5. 也就是说,获取字段类型时,字段tinyint(1)的类型被当做boolean类型进行了返回。导致java中Integer类型无法进行强转。

  解决方法:alter talbe change `xxx` `xxx` tinyint(4) ...;即可。修改tinyint数据类型长度,mysql也就不再当做boolean类型进行返回了。

  总结:Mysql表结构设计时,要避免设计为tinyint(1)这种类型,以免与boolean类型数据结构进行混淆。引起不必要bug。当然也可以总java代码中进行修改,修改后的影响,还需另外评估。

 

  

posted @ 2019-03-14 21:03 LinkLee 阅读(...) 评论(...) 编辑 收藏