MYSQL STRICT_TRANS_TABLES graphic tutorial

MYSQL STRICT_TRANS_TABLES when using the graphic tutorial mysql insert, often prompts no default value, MySQL 1364 error prompt: #1364-Field "XXX" doesn't have a default value. The XXX field does not have a default value. That is to say, we did not assign a value to it, and the default value is not set for this field in the table. This is the strict mode for enabling the storage engine in mysql mode. The invalid data value is denied. I will post the actual operation Question one:

The fields in my Tbl_User table are www.2cto.com.

Solution: In the mysql configuration file my. ini contains XML/HTML code 01. # Set the SQL mode to strict # SQL-mode = "STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION" 02. changed to: 03. 04. SQL-mode = "NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION" Remove the strict mode. If you need to enable mysql, paste the related mode details on the Internet: NO_AUTO_CREATE_USER www.2cto.com to prevent GRANT from automatically creating new users, unless a password is specified. NO_UNSIGNED_SUBTRACTION In the subtraction operation, if an operand has no symbol, do not mark the result as UNSIGNED. Note that the unsigned bigint cannot be 100% used in context. In mysql 5, the default values are REAL_AS_FLOAT, PIPES_AS_CONTACT, ANSI_QUOTES, GNORE_SPACE, and ANSI. In this mode, values exceeding the field length can be inserted, only after insertion, warning rather than error is returned. When STRICT_TRANS_TABLES is used, an error is returned and a strict warning is given. NO_AUTO_CREATE_USER prevents GRANT from automatically creating new users unless a password is specified. The following describes all supported modes in the manual: · ALLOW_INVALID_DATES does not check all dates in strict mode. Only check the day between the month and the day between 1 and 12. This is important in Web applications when you get the year, month, and day from three different fields and want to exactly save the content inserted by the user (without date verification. This mode applies to DATE and DATETIME columns. It is not suitable for the TIMESTAMP column. The TIMESTAMP column requires the verification date. After the strict mode is enabled, the server requires valid months and days, not only in the range of 1 to 12 and 1 to 31, respectively. For example, '2017-04-31 'is valid when strict mode is disabled, but it is invalid when strict mode is enabled. ALLOW_INVALID_DATES should also be enabled to allow the masking of fixed dates in strict mode. Www.2cto.com · ANSI_QUOTES regards 'as an identifier quotation mark ('''), not a string quotation mark character. In ANSI mode, you can still use ''' to reference the identifier. When ANSI_QUOTES is enabled, you cannot use double quotation marks to reference strings because it is interpreted as an identifier. · ERROR_FOR_DIVISION_BY_ZERO is in strict mode. During INSERT or UPDATE, if it is divided by zero (or MOD (X, 0), an error is generated (otherwise it is a warning ). If this mode is not provided, MySQL returns NULL if it is divided by zero. If insert ignore or update ignore is used, MySQL generates a zero division warning, but the operation result is NULL. · The priority of the HIGH_NOT_PRECEDENCE NOT operator is that expressions such as NOT a BETWEEN B AND c are interpreted as NOT (a BETWEEN B AND c ). In some earlier versions of MySQL, the expression is interpreted as (NOT a) BETWEEN B AND c. Enable the HIGH_NOT_PRECEDENCESQL mode to obtain results with a higher priority. Mysql> SET SQL _mode = ''; mysql> select not 1 BETWEEN-5 AND 5;-> 0 mysql> SET SQL _mode = 'Broken _ not '; mysql> select not 1 BETWEEN-5 AND 5;-> 1 · IGNORE_SPACE allows space BETWEEN the function name AND. All function names are considered as saved words. The result is that if you want to access the database, table, or column name that is saved as a word, you must reference it. For example, because there is a USER () function, the user table name in the mysql database and the User column in the table are saved, so you must reference them: SELECT "User" FROM mysql. "user"; · NO_AUTO_CREATE_USER prevents GRANT from automatically creating new users unless a password is specified. Www.2cto.com · NO_AUTO_VALUE_ON_ZERONO_AUTO_VALUE_ON_ZERO affects the processing of the AUTO_INCREMENT column. Generally, you can insert NULL or 0 to this column to generate the next serial number. NO_AUTO_VALUE_ON_ZERO disables 0, so only NULL can generate the next serial number. This mode is useful if you save 0 to the AUTO_INCREMENT column of the table. (This Convention is not recommended ). For example, if you use mysqldump to dump a table and reload the table, MySQL generates a new serial number when it encounters a value of 0. The contents of the generated table are different from those of the dump table. Enable NO_AUTO_VALUE_ON_ZERO before reloading the dump file to solve this problem. Mysqldump automatically includes the NO_AUTO_VALUE_ON_ZERO statement in the output. · NO_BACKSLASH_ESCAPES disables the backslash ('') as the exit character in the string. If this mode is enabled, the backslash is a common character. · When creating a table, NO_DIR_IN_CREATE ignores all index directory And data directory commands. This option is useful for slave replication servers. · NO_ENGINE_SUBSTITUTION if the required storage engine is disabled or not compiled, the storage engine can be replaced automatically. · NO_FIELD_OPTIONS do not print MySQL Special Column options in the output of show create table. This mode is used for mysqldump in portable mode. · NO_KEY_OPTIONS do not print the MySQL dedicated Index option in the output of show create table. This mode is used for mysqldump in portable mode. · NO_TABLE_OPTIONS www.2cto.com do not print the MySQL special TABLE option (such as ENGINE) in the output of show create table ). This mode is used for mysqldump in portable mode. · NO_UNSIGNED_SUBTRACTION In the subtraction operation, if an operand has no symbol, do not mark the result as UNSIGNED. Note that the unsigned bigint cannot be 100% used in context. · NO_ZERO_DATE is in strict mode. Do not use '2017-00-00 'as a valid date. You can still use the IGNORE option to insert a zero date. In non-strict mode, this date is acceptable, but a warning is generated. · NO_ZERO_IN_DATE is in strict mode and the date with the month or day part 0 is not accepted. If the IGNORE option is used, we insert '2017-00-00 'for a similar date ′. In non-strict mode, this date is acceptable, but a warning is generated. · ONLY_FULL_GROUP_BY do not point the query in the group by section to an unselected column. · PIPES_AS_CONCAT treats | as a string connection operator (+) (same as CONCAT (), not as OR. · REAL_AS_FLOAT treats REAL as a synonym for FLOAT, rather than a synonym for DOUBLE. · STRICT_TRANS_TABLES enables strict mode for all storage engines. The invalid data value is denied. · STRICT_TRANS_TABLES enable strict mode for the transaction storage engine, or enable strict mode for the non-transaction storage engine. Detailed descriptions are provided later. Strictly control how MySQL processes illegal or lost input values. One value can be invalid for several reasons. For example, the data type is incorrect. It is not suitable for columns or is out of range. If the newly inserted row does not contain a column that does not display the value defined by the DEFAULT clause, the value is lost. When the STRICT_ALL_TABLES or STRICT_TRANS_TABLES mode is enabled for the transaction table at www.2cto.com, an error occurs if the statement contains an invalid or missing value. The statement is abandoned and rolled. For non-transaction tables, if the 1st rows inserted or updated have bad values, the two modes share the same behavior. The statement is abandoned and the table remains unchanged. If a statement inserts or modifies multiple rows with a bad value of 2nd or later, The result depends on which strict option is enabled: · for STRICT_ALL_TABLES, MySQL returns an error and ignores the remaining rows. However, in this case, the previous row has been inserted or updated. This indicates that you can update some data, which may not be what you want. To avoid this, it is best to use a single row statement, because you can give up without changing the table. · For STRICT_TRANS_TABLES, MySQL converts invalid values to valid values closest to the column and inserts the adjusted values. If the value is lost, MySQL inserts an implicit default value in the column. In any case, MySQL generates a warning instead of an error and continues to execute the statement. Describes the implicit default values. Invalid date is not allowed in strict mode, for example, '2017-04-31 ′. It does not allow the date to use the "zero" part, for example, '2017-04-00' or "zero" date. To disable it, enable the NO_ZERO_IN_DATE and NO_ZERO_DATE SQL modes based on the strict mode. Www.2cto.com if you do not use the strict mode (that is, do not enable the STRICT_TRANS_TABLES or STRICT_ALL_TABLES mode), MySQL inserts the adjusted value for illegal or lost values and gives a warning. In strict mode, you can use insert ignore or update ignore. The following special modes quickly combine the modes listed above. This includes all the mode values in most of the latest MySQL versions. In the old version, the combination mode does not include any unsuitable mode values in the new version. It is equivalent to REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, and IGNORE_SPACE. · DB2 is equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, and NO_FIELD_OPTIONS. · MAXDB is equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, and NO_AUTO_CREATE_USER. · MSSQL is equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, and NO_FIELD_OPTIONS. · MYSQL323 is equivalent to NO_FIELD_OPTIONS and HIGH_NOT_PRECEDENCE. · MYSQL40 www.2cto.com is equivalent to NO_FIELD_OPTIONS and HIGH_NOT_PRECEDENCE. · ORACLE is equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, and NO_AUTO_CREATE_USER. · POSTGRESQL is equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, and NO_FIELD_OPTIONS. · TRADITIONAL is equivalent to STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER (2) SQL code 1. select @ SQL _mode; 2. 3. create table 'T' (4. 'name' varchar (20), 5. 'email 'varchar (40) 6 .); www.2cto.com 7. desc t; 8. 9.set session SQL _mode = "REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, ANSI"; 10. 11. insert into t ('name', 'email ') values ("12345678901234567890999999", "beijing2008@126.com"); 12. 13.set session SQL _mode = "STRICT_TRANS_TABLES"; this article is from zhixing.com.