Shulou(Shulou.com)06/01 Report--

Viewing and setting of SQL Mode in MySQL

MySQL can run in different modes, and can run different modes in different scenarios, depending on the value of the system variable sql_mode. This article mainly introduces the view and setting of this value, mainly under the Mac system.

For the meaning and role of each model, it is easy to find online, this article will not introduce.

It can be divided into three levels according to area and time, namely, session level, global level, and configuration (permanent) level.

Session level:

View-

Select @ @ session.sql_mode

Modify-

Set @ @ session.sql_mode='xx_mode'set session sql_mode='xx_mode'

Session can be omitted. Default session, valid only for the current session.

Global level:

View-

Select @ @ global.sql_mode

Modify-

Set global sql_mode='xx_mode';set @ @ global.sql_mode='xx_mode'

Advanced permission is required, which only takes effect on the next connection and does not affect the current session (tested), and will expire after MySQL restart, because the corresponding values in the configuration file will be re-read when MySQL is restarted. If you want to take effect permanently, you need to modify the values in the configuration file.

Configuration modification (permanent):

Open vi / etc/my.cnf

Add below

[mysqld] sql-mode = "xx_mode"

Note: [mysqld] must be added and there is a "-" in the middle of the sql-mode, not an underscore.

Save the exit, restart the server, and it will take effect permanently.

Because there is no configuration file for installing MySQL under Mac, you need to add it manually.

Ps

Finally, add a little extra, that is, the start, stop, restart and other operations of MySQL under Mac.

There are two main ways

First, click the "system preferences" corresponding to the MySQL panel, you can achieve management.

The second is the command line.

For MySQL-related execution scripts, the following two are commonly used:

/ usr/local/mysql/support-files/mysql.server/usr/local/mysql/bin/mysql

Mysql.server is the operation that controls the start and stop of the server.

Mysql.server start | stop | restart | status

Mysql is mainly used to connect to the server.

Mysql-uroot-p *-h * *-D * *

Some require sudo permission, and relevant paths can be added to environment variables to simplify writing, without introducing how to add them.

Knowledge point expansion:

Strict Mode exposition

Based on the limitations of strict mode (STRICT_TRANS_TABLES) of mysql5.0 and above:

1)。 Inserting null values into not null fields is not supported

2)。 Inserting a''value for a self-growing field is not supported, but a null value can be inserted

3)。 Default values for text fields are not supported

Look at the following code: (the first field is a self-increment field)

$query= "insert into demo values (','$firstname','$lastname','$sex')"

The above code is only valid in non-strict mode.

Code code

$query= "insert into demo values (NULL,'$firstname','$lastname','$sex')"

The above code is valid only in strict mode. Replace the null value''with NULL.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.