• Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

Preface:

Today also didn’t want to send a good write what ~ in CSDN inventory to work in a pit before blog.csdn.net/pmdream/art…

​

directory

Preface:

Conclusion:

The test code

Test 1: tinyint(1) rs.getobject (2), getTrue /false

Test 2: tinyint(2) rs.getobject (2), get a number

Navicat query tinyint (1)

Int (int); int (int)

Preface:

Check the data quality of the mysql to Hive synchronization.

Whether the fields are found consistently.

Tinyint (1) in mysql if getObject counts as true or false.

Conclusion:

Because we’ve seen before, the length inside the parentheses in mysql only affects presentation, which is the complement of 0.

Tinyint (1); tinyint(2); tinyint(1); tinyint(2) Because all numbers corresponding to Hive are saved, there is a problem when we synchronize data to check whether the data is the same.

The toString field in mysql is false and the hive field is 0.

Why is this a problem?

Because the JDBC code will automatically convert tinyint(1) to true/false in the case of getObject. Tinyint (1) is considered a bit. (True if greater than or equal to 1)

For JDBC, the tinyint(2) type is considered tinyint

If it’s tinyint(1) and if it’s getInt, it’ll display an integer between -128 and 127.

So it’s the framework that does the processing that leads to the cognitive bias.

A tinyint takes only one byte;

Inyint fields that are set to UNSIGNED can only store integers from 0 to 255. They cannot be used to store negative numbers. Fields of type tinyint are stored as integers from -128 to 127 if the type is not set to UNSIGNED.

The test code
` ` `
public static void main(String[] args) throws Exception { Connection connection = getConnection(); PreparedStatement pstmt = connection.prepareStatement(“select id, del from tinyint_test “); ResultSet rs = pstmt.executeQuery(); ResultSetMetaData metaData = rs.getMetaData(); int columnCount = metaData.getColumnCount(); for (int i = 0; i < columnCount; I++) {System. Out. Println (” first “+ I +” type: “+ metaData. GetColumnTypeName (I + 1)); } while (rs.next()) {system.out.println (“id:” + rs.getobject (1) + “Tinyint:” + rs.getobject (2)); } connection.close(); } public static Connection getConnection() { Connection conn = null; try { Class.forName(“com.mysql.cj.jdbc.Driver”); String url = “jdbc:mysql://localhost:3306/dk_test”; conn = DriverManager.getConnection(url, “root”, “root”); } catch (Exception e) { e.printStackTrace(); } return conn; }
` ` `

Test 1: tinyint(1) rs.getobject (2), getTrue /false
` ` `
CREATE TABLE tinyint_test ( id int NOT NULL AUTO_INCREMENT COMMENT ‘id’, del tinyint(1) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
` ` `

​

​

The output

Type: INT type: BIT ID :1 Tinyint:false ID :2 Tinyint:true ID :3 Tinyint:true ID :4

Test 2: Tinyint (2)Rs.getobject (2), which gets numbers

​

Output result:

Column 0 type: INT type: TINYINT ID :1 Column 2 TINYINT :0 ID :2 Column 2 TINYINT :1 ID :3 Column 2 TINYINT :2 ID :4 Column 2 TINYINT :3

Navicat query tinyint (1)

If you display tinyint(1) on navicat, you can display numbers properly

​

Int (int); int (int)

If the code is changed to rs.getint (2) below, instead of object, the normal numbers are displayed:

Println ("id:" + rs.getobject (1) + "Tinyint:" + rs.getint (2)); println("id:" + rs.getobject (1) +" Tinyint:" + rs.getint (2)); Result: Tinyint:0 id:2 Tinyint:1 ID :3 Tinyint:2 ID :4 Tinyint:3Copy the code

​