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

绝地灬酷狼 2022-02-13 00:39 256阅读 0赞

熟悉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.
> 
> <table style="border-collapse:collapse;border-spacing:0px;border:1px solid #C0C0C0;"> 
> <thead> 
> <tr> 
> <th style="border:1px solid #C0C0C0;border-collapse:collapse;">Other Vendor Type</th> 
> <th style="border:1px solid #C0C0C0;border-collapse:collapse;">MySQL Type</th> 
> </tr> 
> </thead> 
> <tbody> 
> <tr> 
> <td style="border:1px solid #C0C0C0;border-collapse:collapse;"><a title="11.2.1&nbsp;Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT" href="https://dev.mysql.com/doc/refman/5.7/en/integer-types.html" style="color:#000000;" rel="nofollow"><code>BOOL</code></a></td> 
> <td style="border:1px solid #C0C0C0;border-collapse:collapse;"><a title="11.2.1&nbsp;Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT" href="https://dev.mysql.com/doc/refman/5.7/en/integer-types.html" style="color:#000000;" rel="nofollow"><code>TINYINT</code></a></td> 
> </tr> 
> <tr> 
> <td style="border:1px solid #C0C0C0;border-collapse:collapse;"><a title="11.2.1&nbsp;Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT" href="https://dev.mysql.com/doc/refman/5.7/en/integer-types.html" style="color:#000000;" rel="nofollow"><code>BOOLEAN</code></a></td> 
> <td style="border:1px solid #C0C0C0;border-collapse:collapse;"><a title="11.2.1&nbsp;Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT" href="https://dev.mysql.com/doc/refman/5.7/en/integer-types.html" style="color:#000000;" rel="nofollow"><code>TINYINT</code></a></td> 
> </tr> 
> <tr> 
> <td style="border:1px solid #C0C0C0;border-collapse:collapse;"><code>CHARACTER VARYING(<code>M</code>)</code></td> 
> <td style="border:1px solid #C0C0C0;border-collapse:collapse;"><code>VARCHAR(<code>M</code>)</code></td> 
> </tr> 
> <tr> 
> <td style="border:1px solid #C0C0C0;border-collapse:collapse;"><a title="11.2.2&nbsp;Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC" href="https://dev.mysql.com/doc/refman/5.7/en/fixed-point-types.html" style="color:#000000;" rel="nofollow"><code>FIXED</code></a></td> 
> <td style="border:1px solid #C0C0C0;border-collapse:collapse;"><a title="11.2.2&nbsp;Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC" href="https://dev.mysql.com/doc/refman/5.7/en/fixed-point-types.html" style="color:#000000;" rel="nofollow"><code>DECIMAL</code></a></td> 
> </tr> 
> <tr> 
> <td style="border:1px solid #C0C0C0;border-collapse:collapse;"><a title="11.2.3&nbsp;Floating-Point Types (Approximate Value) - FLOAT, DOUBLE" href="https://dev.mysql.com/doc/refman/5.7/en/floating-point-types.html" style="color:#000000;" rel="nofollow"><code>FLOAT4</code></a></td> 
> <td style="border:1px solid #C0C0C0;border-collapse:collapse;"><a title="11.2.3&nbsp;Floating-Point Types (Approximate Value) - FLOAT, DOUBLE" href="https://dev.mysql.com/doc/refman/5.7/en/floating-point-types.html" style="color:#000000;" rel="nofollow"><code>FLOAT</code></a></td> 
> </tr> 
> <tr> 
> <td style="border:1px solid #C0C0C0;border-collapse:collapse;"><a title="11.2.3&nbsp;Floating-Point Types (Approximate Value) - FLOAT, DOUBLE" href="https://dev.mysql.com/doc/refman/5.7/en/floating-point-types.html" style="color:#000000;" rel="nofollow"><code>FLOAT8</code></a></td> 
> <td style="border:1px solid #C0C0C0;border-collapse:collapse;"><a title="11.2.3&nbsp;Floating-Point Types (Approximate Value) - FLOAT, DOUBLE" href="https://dev.mysql.com/doc/refman/5.7/en/floating-point-types.html" style="color:#000000;" rel="nofollow"><code>DOUBLE</code></a></td> 
> </tr> 
> <tr> 
> <td style="border:1px solid #C0C0C0;border-collapse:collapse;"><a title="11.2.1&nbsp;Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT" href="https://dev.mysql.com/doc/refman/5.7/en/integer-types.html" style="color:#000000;" rel="nofollow"><code>INT1</code></a></td> 
> <td style="border:1px solid #C0C0C0;border-collapse:collapse;"><a title="11.2.1&nbsp;Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT" href="https://dev.mysql.com/doc/refman/5.7/en/integer-types.html" style="color:#000000;" rel="nofollow"><code>TINYINT</code></a></td> 
> </tr> 
> <tr> 
> <td style="border:1px solid #C0C0C0;border-collapse:collapse;"><a title="11.2.1&nbsp;Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT" href="https://dev.mysql.com/doc/refman/5.7/en/integer-types.html" style="color:#000000;" rel="nofollow"><code>INT2</code></a></td> 
> <td style="border:1px solid #C0C0C0;border-collapse:collapse;"><a title="11.2.1&nbsp;Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT" href="https://dev.mysql.com/doc/refman/5.7/en/integer-types.html" style="color:#000000;" rel="nofollow"><code>SMALLINT</code></a></td> 
> </tr> 
> <tr> 
> <td style="border:1px solid #C0C0C0;border-collapse:collapse;"><a title="11.2.1&nbsp;Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT" href="https://dev.mysql.com/doc/refman/5.7/en/integer-types.html" style="color:#000000;" rel="nofollow"><code>INT3</code></a></td> 
> <td style="border:1px solid #C0C0C0;border-collapse:collapse;"><a title="11.2.1&nbsp;Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT" href="https://dev.mysql.com/doc/refman/5.7/en/integer-types.html" style="color:#000000;" rel="nofollow"><code>MEDIUMINT</code></a></td> 
> </tr> 
> <tr> 
> <td style="border:1px solid #C0C0C0;border-collapse:collapse;"><a title="11.2.1&nbsp;Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT" href="https://dev.mysql.com/doc/refman/5.7/en/integer-types.html" style="color:#000000;" rel="nofollow"><code>INT4</code></a></td> 
> <td style="border:1px solid #C0C0C0;border-collapse:collapse;"><a title="11.2.1&nbsp;Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT" href="https://dev.mysql.com/doc/refman/5.7/en/integer-types.html" style="color:#000000;" rel="nofollow"><code>INT</code></a></td> 
> </tr> 
> <tr> 
> <td style="border:1px solid #C0C0C0;border-collapse:collapse;"><code>INT8</code></td> 
> <td style="border:1px solid #C0C0C0;border-collapse:collapse;"><a title="11.2.1&nbsp;Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT" href="https://dev.mysql.com/doc/refman/5.7/en/integer-types.html" style="color:#000000;" rel="nofollow"><code>BIGINT</code></a></td> 
> </tr> 
> <tr> 
> <td style="border:1px solid #C0C0C0;border-collapse:collapse;"><code>LONG VARBINARY</code></td> 
> <td style="border:1px solid #C0C0C0;border-collapse:collapse;"><a title="11.4.3&nbsp;The BLOB and TEXT Types" href="https://dev.mysql.com/doc/refman/5.7/en/blob.html" style="color:#000000;" rel="nofollow"><code>MEDIUMBLOB</code></a></td> 
> </tr> 
> <tr> 
> <td style="border:1px solid #C0C0C0;border-collapse:collapse;"><code>LONG VARCHAR</code></td> 
> <td style="border:1px solid #C0C0C0;border-collapse:collapse;"><a title="11.4.3&nbsp;The BLOB and TEXT Types" href="https://dev.mysql.com/doc/refman/5.7/en/blob.html" style="color:#000000;" rel="nofollow"><code>MEDIUMTEXT</code></a></td> 
> </tr> 
> <tr> 
> <td style="border:1px solid #C0C0C0;border-collapse:collapse;"><code>LONG</code></td> 
> <td style="border:1px solid #C0C0C0;border-collapse:collapse;"><a title="11.4.3&nbsp;The BLOB and TEXT Types" href="https://dev.mysql.com/doc/refman/5.7/en/blob.html" style="color:#000000;" rel="nofollow"><code>MEDIUMTEXT</code></a></td> 
> </tr> 
> <tr> 
> <td style="border:1px solid #C0C0C0;border-collapse:collapse;"><a title="11.2.1&nbsp;Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT" href="https://dev.mysql.com/doc/refman/5.7/en/integer-types.html" style="color:#000000;" rel="nofollow"><code>MIDDLEINT</code></a></td> 
> <td style="border:1px solid #C0C0C0;border-collapse:collapse;"><a title="11.2.1&nbsp;Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT" href="https://dev.mysql.com/doc/refman/5.7/en/integer-types.html" style="color:#000000;" rel="nofollow"><code>MEDIUMINT</code></a></td> 
> </tr> 
> <tr> 
> <td style="border:1px solid #C0C0C0;border-collapse:collapse;"><a title="11.2.2&nbsp;Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC" href="https://dev.mysql.com/doc/refman/5.7/en/fixed-point-types.html" style="color:#000000;" rel="nofollow"><code>NUMERIC</code></a></td> 
> <td style="border:1px solid #C0C0C0;border-collapse:collapse;"><a title="11.2.2&nbsp;Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC" href="https://dev.mysql.com/doc/refman/5.7/en/fixed-point-types.html" style="color:#000000;" rel="nofollow"><code>DECIMAL</code></a></td> 
> </tr> 
> </tbody> 
> </table>
> 
> 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.  其中中间件中有如下解析部分：
    
    ![复制代码][copycode.gif]
    
        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");
                    }
        
                }
            }
    
    ![复制代码][copycode.gif]
4.  怎样获取的数字类型呢，代码如下：
    
    ![复制代码][copycode.gif]
    
        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;
            }
    
    ![复制代码][copycode.gif]
5.  也就是说，字段tinyint(1)被当做boolean类型进行了返回。导致java中Integer类型无法进行强转。

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

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

[copycode.gif]: /images/20220213/6eee7d7eb187411ba4ef99c32e07cb8d.png