MySQL SQL _mode and MySQLsql_mode

MySQL SQL _mode and MySQLsql_mode

Example:

Table test

CREATE TABLE `test` (  `a` varchar(255) NOT NULL,  `b` varchar(255) DEFAULT NULL,  `c` int(11) DEFAULT NULL,  `d` int(11) NOT NULL DEFAULT '0') ENGINE=InnoDB DEFAULT CHARSET=utf8;

If you execute the SQL statement insert into test values ('',''); the error 1366-Incorrect integer value: ''for column 'C' at row 1

Find the my. cnf file. The original MySQL SQL _mode mode is set to SQL _mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES, and the table engine is InnoDB, so the above error is reported.

STRICT_TRANS_TABLES mode: strictly checks data in strict mode. It only enables strict mode for tables that support transactions (InnoDB). The error data cannot be inserted and an error is reported.

NO_ENGINE_SUBSTITUTION mode: If the storage engine is disabled or not compiled, an error is thrown. If the storage engine is not started, the default storage engine is used instead and an exception is thrown.

MySQL configuration file my. by default, MySQL's SQL _mode mode is not set in ini. When the preceding statement is executed, no error is reported. When a field of the int type is inserted with a null value, it is saved as 0.

Other SQL _mode modes:

STRICT_ALL_TABLES mode: strict mode is enabled for all engine tables. In this mode, the current operation is terminated once any operation data has problems. For non-transaction engines that enable STRICT_ALL_TABLES, the data may stay in an unknown state, therefore, be very careful about the potential impact of this option.

ANSI mode: loose mode, which verifies the inserted data. If the data type or length does not conform to the defined type or length, adjust or cut the data type and save the data, a warning is reported.

TRADITIONAL mode: strict mode. when data is inserted into the mysql database, strict data validation is performed to ensure that the error data cannot be inserted and an error is reported. When used for a transaction, the transaction will be rolled back.