Detailed explanation of MySql super long automatic truncation example-Mysql Tutorial-php.cn

Detailed explanation of MySql super long automatic truncation example

小云云

Release： 2017-12-25 14:38:47

Original

2439 people have browsed it

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? This article mainly introduces the relevant information of MySql super long automatic truncation examples. Here is an example to illustrate how to implement the automatic truncation function. Friends who need it can refer to it. I hope it can help everyone.

Follows

CREATE TABLE `p_app_station` (
 `WX_APP_ID` varchar(20) NOT NULL,
 `APP_SECRET` varchar(33) DEFAULT NULL,
 `IS_BINDING` int(1) DEFAULT &#39;0&#39;,
 `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 &#39;标记类型（试用版:0,会员版:1,定制版:2）&#39;,
 `ACTIVE_DATE` datetime DEFAULT NULL COMMENT &#39;使用时间截止&#39;,
 `APP_MODULE_ID` varchar(60) DEFAULT NULL COMMENT &#39;推送模版消息ID&#39;,
 PRIMARY KEY (`WX_APP_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Copy after login

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

Copy after login

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 &#39;%s&#39; at row %ld

Copy after login

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=&#39;");
 
        if (currentSqlMode != null && currentSqlMode.length() > 0) {
          commandBuf.append(currentSqlMode);
          commandBuf.append(",");
        }
 
        commandBuf.append("STRICT_TRANS_TABLES&#39;");
 
        execSQL(null, commandBuf.toString(), -1, null, DEFAULT_RESULT_SET_TYPE, DEFAULT_RESULT_SET_CONCURRENCY, false, this.database, null, false);
 
        setJdbcCompliantTruncation(false); // server&#39;s handling this for us now
      } else if (strictTransTablesIsSet) {
        // We didn&#39;t set it, but someone did, so we piggy back on it
        setJdbcCompliantTruncation(false); // server&#39;s handling this for us now
      }
 
    }
  }
}

Copy after login

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);

Copy after login

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.

Related recommendations:

PHP Chinese string truncation without garbled solution

Text type field in MSSQL database in PHP Solution to being truncated in

Fragmentation and truncation sequence in python

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