Summary of the characteristics of SQL mode in MySQL

Preface

The SQL mode affects the SQL syntax that MySQL supports and the data validation checks that it performs.

The MySQL server can operate in different SQL modes, and can apply these modes differently for different clients, depending on the value of the sql_mode system variable. The DBA can set the global SQL mode to match site server operating requirements, and each application can set its session SQL mode to its own requirements.

The mode affects the SQL syntax that MySQL supports and the data validation checks it performs. This makes it easier to use MySQL in different environments and to use MySQL with other database servers.

Let’s take a look at the detailed introduction.

Setting the SQL Mode

To change the SQL mode at runtime, set the global or session sql_mode system variable using the SET statement.

SET GLOBAL sql_mode = 'modes';
SET SESSION sql_mode = 'modes';

Mode List

For more details, please refer to https://dev.mysql.com/doc/ref...

Strict SQL Mode

The MySQL server can operate in different SQL modes, and can apply these modes differently for different clients, depending on the value of the sql_mode system variable. In strict SQL mode, the server upgrades certain warnings to errors.

Strict SQL mode applies to the following statements

• ALTER TABLE
• CREATE TABLE
• CREATE TABLE ... SELECT
• DELETE
• INSERT
• LOAD DATA
• LOAD XML
• SELECT SLEEP()
• UPDATE

Within a stored procedure, if the procedure was defined while strict mode was in effect, single statements of the listed types are executed in strict SQL mode.

Strict SQL mode applies to the following errors, which represent a class of errors where an input value is invalid or missing. A value is invalid if it has the wrong data type for the column or might be out of range. If a new row to be inserted does not contain a value for a column that has no explicit DEFAULT clause in its definition that is NOT NULL , then the value is missing.

• ER_BAD_NULL_ERROR
• ER_CUT_VALUE_GROUP_CONCAT
• ER_DATA_TOO_LONG
• ER_DATETIME_FUNCTION_OVERFLOW
• ER_DIVISION_BY_ZERO
• ER_INVALID_ARGUMENT_FOR_LOGARITHM
• ER_NO_DEFAULT_FOR_FIELD
• ER_NO_DEFAULT_FOR_VIEW_FIELD
• ER_TOO_LONG_KEY
• ER_TRUNCATED_WRONG_VALUE
• ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
• ER_WARN_DATA_OUT_OF_RANGE
• ER_WARN_NULL_TO_NOTNULL
• ER_WARN_TOO_FEW_RECORDS
• ER_WRONG_ARGUMENTS
• ER_WRONG_VALUE_FOR_TYPE
• WARN_DATA_TRUNCATED

Acknowledgements

Thank you for reading this. I hope this article can help you. Thank you.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.