preface

the

A recent version required to change the comment length limit from 64 to 500. During the debugging process, only 200 characters could be displayed in the comment. After checking the library, it was found that the field length was set to 200 and truncated when inserting. Isn’t it because the paper is wrong? Well, it should be sql_mode.

validation

Create table username8
CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(8) DEFAULT NULL,
  `pwd` varchar(8) DEFAULT NULL,
  `seq` int(11) DEFAULT NULL.PRIMARY KEY (`id`),
  KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=168 DEFAULT CHARSET=utf8; ## Insert username of length9The value of theINSERT INTO `test`(`username` , `pwd` , `seq`)
VALUES
	(
		'123456789' ,
		'133aaa' ,
		NULL); ## Successfully insertedNo errors;1 row affected,taking 3.2Ms ## query found truncated valueselect username from test;
> 12345678Sql_mode # # changeset SESSION sql_mode  = 'STRICT_ALL_TABLES'; ## Insert a value of the same length againINSERT INTO `test`(`username` , `pwd` , `seq`)
VALUES
	(
		'123456789' ,
		'133aaa' ,
		NULL); ## error Data too longfor column 'username' at row 1

Copy the code

SQL_MODE

MySQL server can run in different mode Settings (SQL_mode) and can be set differently for different clients using the SQL_mode variable.

Effect:

  • grammar
  • Data validation

Set the SQL_MODE

The default SQL_MODE value is NO_ENGINE_SUBSTITUTION, which can be set at startup and run time.

Startup Settings:

  • -sql-mode =”A,B,C…” (Separate multiple values with commas (,).)
  • Add sql-mode=”A,B,C… “to my.cnf config file (Separate multiple values with commas (,).)

Runtime Settings:

SET GLOBAL sql_mode = 'A, B, C... '; Global reconnection is in effectSET SESSION sql_mode = 'A, B, C... '; ## The current session takes effect immediatelyCopy the code

To view:

- SELECT @@GLOBAL.sql_mode 
- SELECT @@SESSION.sql_mode 
Copy the code

Note:

  • It is best not to change SQL_mode after the table is partitioned, because different SQL_mode can affect some calculation results and have an impact on partitioning policies
  • Primary and secondary servers, using the same SQL_mode to achieve data synchronization

SQL_MODE optional value

Several other values that affect syntax and data validation

  • ALLOW_INCALID_DATES

    This parameter is valid only for DATE and DATETIME, but not for TIMESTAMP.

    insert into temp values('the 2019-02-31 01:01:01');

Incorrect datetime value: 'the 2019-02-31 01:01:01' for column 'dt' at row 1
Copy the code
    set SESSION sql_mode  = 'ALLOW_INVALID_DATES';
insert into temp values('the 2019-02-31 01:01:01');

No errors;1 row affected,taking 3.2 ms
Copy the code

  • ANSI_QUOTES

    Double quotation marks (“) are used as identifier reference characters, the same as’ ‘.

    truncate table "temp";

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"temp"' at line 1
Copy the code
    set SESSION sql_mode  = 'ANSI_QUOTES';
truncate table "temp";

No errors;0 row affected,taking 0.2 ms
Copy the code

  • ERROR_FOR_DEVISION_BY_ZERO

    If STRICT_TRANS_TABLES or STRICT_ALL_TABLES is enabled at this time, the statement execution will be blocked and an error will be reported. The SELECT statement is not affected.

  • HIGH_NOT_PRECEDENCE

    Increase the priority of logical operation NOT

    select not 1;
> 0

select 1 between - 1 and 2;
> 1

select not 1 between - 1 and 2;
> 0Set sql_mode # #set SESSION sql_mode  = 'HIGH_NOT_PRECEDENCE';
select not 1 between - 1 and 2;
> 0
Copy the code

  • ONLY_FULL_GROUP_BY

    Affects the group by syntax

    select count(0) ct,username from test group by pwd;
> 2 | 12345678Set sql_mode # #set SESSION sql_mode  = 'ONLY_FULL_GROUP_BY';

select count(0) ct,username from test group by pwd;
Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.test.username' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Copy the code

More and more

There are a few options listed above that have a significant impact on coding, and more options are described on the official website.

conclusion

Sql_mode = “MySQL”; sql_mode = “MySQL”; O (studying studying) O ha! .