Detailed explanation of MySql automatic truncation example

A friend asked a question, why can't overlong words be automatically truncated when calling insert or update statements in the project, but can be automatically truncated when directly executed in navicat?

as follows

CREATE TABLE `p_app_station` (
 `WX_APP_ID` varchar(20) NOT NULL,
 `APP_SECRET` varchar(33) DEFAULT NULL,
 `IS_BINDING` int(1) DEFAULT '0',
 `ACCOUNT_ID` int(13) DEFAULT NULL,
 `TOKEN` varchar(40) DEFAULT NULL,
 `BIND_URL` varchar(200) DEFAULT NULL,
 `WX_APP_NAME` varchar(50) DEFAULT NULL,
 `WX_APP_SID` varchar(50) DEFAULT NULL,
 `WX_NO` varchar(50) DEFAULT NULL,
 `CREATE_USER_ID` varchar(13) DEFAULT NULL,
 `UPDATE_DATE` datetime DEFAULT NULL,
 `CREATE_DATE` datetime DEFAULT NULL,
 `UPDATE_USER_ID` varchar(13) DEFAULT NULL,
 `STATION_TYPE` int(1) unsigned zerofill DEFAULT NULL COMMENT 'Mark type (trial version: 0, member version: 1, custom version: 2)',
 `ACTIVE_DATE` datetime DEFAULT NULL COMMENT 'Expiration date',
 `APP_MODULE_ID` varchar(60) DEFAULT NULL COMMENT 'Push template message ID',
 PRIMARY KEY (`WX_APP_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

insert into p_app_station(wx_app_id) values('12121312312312啊啊啊啊啊aassasdasd');
select * from p_app_station where wx_app_id like '12121312312312%';

Obviously varchar(20) is not big enough to hold 12121312312312 ah ah ah aassasdasd

The query results are as follows

It was indeed truncated automatically, but when executing the same SQL in the project, it was found that this was not the case, and an error was reported instead.

Data truncated for column '%s' at row %ld

Considering that it is the same database and there is no difference in schema, the possibility should be on jdbcDriver.

View jdbc source code

private void setupServerForTruncationChecks() throws SQLException {
  if (getJdbcCompliantTruncation()) {
    if (versionMeetsMinimum(5, 0, 2)) {
      String currentSqlMode = this.serverVariables.get("sql_mode");
 
      boolean strictTransTablesIsSet = StringUtils.indexOfIgnoreCase(currentSqlMode, "STRICT_TRANS_TABLES") != -1;
 
      if (currentSqlMode == null || currentSqlMode.length() == 0 || !strictTransTablesIsSet) {
        StringBuilder commandBuf = new StringBuilder("SET sql_mode='");
 
        if (currentSqlMode != null && currentSqlMode.length() > 0) {
          commandBuf.append(currentSqlMode);
          commandBuf.append(",");
        }
 
        commandBuf.append("STRICT_TRANS_TABLES'");
 
        execSQL(null, commandBuf.toString(), -1, null, DEFAULT_RESULT_SET_TYPE, DEFAULT_RESULT_SET_CONCURRENCY, false, this.database, null, false);
 
        setJdbcCompliantTruncation(false); // server's handling this for us now
      } else if (strictTransTablesIsSet) {
        // We didn't set it, but someone did, so we piggy back on it
        setJdbcCompliantTruncation(false); // server's handling this for us now
      }
 
    }
  }
}

View the getJdbcCompliantTruncation method, its default value is

private BooleanConnectionProperty jdbcCompliantTruncation = new BooleanConnectionProperty("jdbcCompliantTruncation", true,
    Messages.getString("ConnectionProperties.jdbcCompliantTruncation"), "3.1.2", MISC_CATEGORY, Integer.MIN_VALUE);

Therefore, from version 3.1.2, if jdbcCompliantTruncation is not set in jdbcurl, the default will be to execute without truncation and report an error.

So is it possible to add parameters?

Make a choice:

If the value is longer than the specified length, there is a risk of loss of precision.

Therefore, it is recommended to check it in the program.

Currently working on the use of hibernate validate.

The above is the explanation about MySQL truncation. If you have any questions, you can leave a message or come to the community of this site to discuss and make progress together. Thank you for reading and I hope it can help everyone. Thank you for your support of this site!

You may also be interested in: