preface

      Is the mysql version upgraded or the problem after installing mysql added to mysql after mysql 5.7.5_ Node verification. Many functions may not be standardized and cannot be used.

1. Mode I     Simple rough method

set @@global.sql_mode  ='';   -- Directly in mysql The console sets the check to null

  After the verification is directly set to null, mysql will automatically restore the configuration after restarting the computer (equal to no solution)

2. Mode II   (Mac looks directly here)

Here, for Mac, Windows is actually the same  

one     We want to create my.cnf under / etc
2.   $ cd /etc
3.   $ sudo vim my.cnf   

Copy the following document to my.cnf,   If wq cannot exit,   Use wq! Command, not yet w! Sudo tee% force save

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL

[client]
default-character-set=utf8

[mysqld]
log-error=/usr/local/mysql/data/mysqld.log
# skip-grant-tables
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

4. The mysql service needs to be restarted at this time. You must restart it with a command

  5. Execute the following command to restart. If Stop MySQL Server is not turned off, it cannot be turned off.

sudo /usr/local/mysql/support-files/mysql.server restart

  be careful:   The close MySQL button in the settings conflicts with the restart command.

            The service is not turned off in the settings. You can't restart using the command. Similarly, services started with commands cannot be turned off in the settings.

sql_ The common values of mode are as follows:  

  1. If you don't want to verify the sql, put the sql_node is configured as null, and the function is added as soon as it is standardized.

ONLY_FULL_GROUP_BY:

For the GROUP BY aggregation operation, if the column in the SELECT does not appear in the GROUP BY, the SQL is illegal because the column is not in the GROUP BY clause

NO_AUTO_VALUE_ON_ZERO:

This value affects the insertion of self growing columns. By default, insert 0 or NULL to generate the next self increasing value. This option is useful if the user wants to insert a value of 0 and the column is self growing.

STRICT_TRANS_TABLES:

In this mode, if a value cannot be inserted into a transaction table, the current operation will be interrupted and the non transaction table will not be restricted

NO_ZERO_IN_DATE:

Zero date and month are not allowed in strict mode

NO_ZERO_DATE:

If this value is set, mysql database does not allow zero date insertion. Inserting zero date will throw an error instead of a warning.

ERROR_FOR_DIVISION_BY_ZERO:

During INSERT or UPDATE, if the data is divided by zero, an error is generated instead of a warning. If this mode is not given, MySQL returns NULL when the data is divided by zero

NO_AUTO_CREATE_USER:

GRANT is not allowed to create a user with a blank password

NO_ENGINE_SUBSTITUTION:

If the required storage engine is disabled or not compiled, an error is thrown. When this value is not set, the default storage engine is used instead and an exception is thrown

PIPES_AS_CONCAT:

Treating "|" as a concatenation operator of a string rather than an or operator is the same as in Oracle database and is similar to the Concat function of a string

ANSI_QUOTES:

Enable ANSI_ After quotes, the string cannot be referenced in double quotes because it is interpreted as an identifier

Original link: mac mysql sqlmode_MySQL sql_mode mode description and setting_ I'm your Lao Zhen's blog - CSDN blog

sql_ What is node?

        sql_mode is a variable that can easily be ignored. The default value is null. Under this setting, some illegal operations can be allowed, such as the insertion of some illegal data. (this refers to the development environment)

          In the production environment, this value must be set to strict mode, so the database of the development and test environment must also be set, so that problems can be found in the development and test phase.