Detailed explanation of the case of over-long automatic truncation in MySql

This article mainly introduces the relevant information of MySql super long automatic truncation example. Here is an example to illustrate how to realize the automatic truncation function. Friends in need can refer to

MySql super long automatic truncation example. Detailed explanation

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

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 '标记类型（试用版:0,会员版:1,定制版:2）',
 `ACTIVE_DATE` datetime DEFAULT NULL COMMENT '使用时间截止',
 `APP_MODULE_ID` varchar(60) DEFAULT NULL COMMENT '推送模版消息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 enough to accommodate 12121312312312ahahahahah aassasdasd

The query results are as follows

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

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

Considering that it is the same database and there are no different modes, the possibility should appear on jdbcDriver.

View the 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, no truncation will be performed by default and an error will be reported.

So is it possible to add parameters?

Make a trade-off:

If the truncation is longer than the value, there may be a risk of loss of accuracy.

Therefore, it is recommended to check in the program.

We are currently working on using hibernate validate.

The above is the detailed content of Detailed explanation of the case of over-long automatic truncation in MySql. For more information, please follow other related articles on the PHP Chinese website!