Shulou(Shulou.com)05/31 Report--

What this article shares with you is about how the Procedure parameters of Mysql are analyzed for NULL problems. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

Recently, I found an interesting thing when I was writing the process. Mysql's procedure encountered some "illegal" parameters in the process of passing parameters. It has its own unique processing method. For example, the parameter originally defined as int is passed in as null.

The procedure of mysql will be executed normally.

Library table structure:

Create database db5

Use db5

Drop table if exists t

Create table t (

Id int primary key auto_increment

Value int

);

Create table T2 (

Id int primary key auto_increment

Value float

);

Create a procedure:

Delimiter / /

CREATE PROCEDURE p14 (IN parameter1 INT)

BEGIN

DECLARE variable1 INT

SET variable1 = parameter1 + 1

INSERT INTO t (value) VALUES (variable1)

END

/ /

Delimiter

Running result:

Mysql > call p14 (5)

Query OK, 1 row affected (0.02 sec)

Mysql > select * from t

+-+ +

| | id | value |

+-+ +

| | 2 | 6 |

+-+ +

1 row in set (0.00 sec)

Mysql > call p14 (null)

Query OK, 1 row affected (0.04 sec)

Mysql > select * from t

+-+ +

| | id | value |

+-+ +

| | 2 | 6 |

| | 3 | NULL |

+-+ +

2 rows in set (0.00 sec)

Have you noticed that when the parameter parameter1 is equal to 5, the table inserts 6, and the data is normal.

When the parameter parameter1 is passed as null, the table is inserted into NULL, which is why.

At this point, you can take a look at the statement that declares variables. The document explains that the statement declare is used to declare local variables. To provide a default value for a variable, include a DEFAULT clause. Value can be specified as an expression and does not need to be a constant. If there is no DEFAULT clause, the initial value is NULL.

This leads to a new problem: NULL=NULL+1? Haha, it's kind of interesting. What's a reasonable explanation for SET variable1 = parameter1 + 1 at this time?

This is the explanation given by Mr. Wang (the second one is a classic):

1 null+1=null

Because null behaves as a "pointer-like", that is, content pointing to "address 0", if this content is "null", it appears as null. This is why the specified INT is also empty. However, if "content" has a value, it is not empty, for MYSQL, it is a "random number" or 0; when the address content is stored, the value is fixed.

2 if A=B+1, An is NULL;SET A=B+1 only if B is null, whether it can be understood as SET (Ball1), A has been replaced 'currently', so it doesn't matter who An is, what matters is Ball1.

This idea has not been verified, mainly can not be separated from SET, and mysql5 documents, there is support for this view, but the English version is used to "replace", not the Chinese "setting" expression, the meaning is more similar! (SET)

A new question: can it run successfully when Aspir1According to BBQ 0?

Mysql >

Delimiter / /

CREATE PROCEDURE p15 (IN parameter1 INT)

BEGIN

Declare variable2 float (5par 3)

SET variable2 = 1 / parameter1

INSERT INTO T2 (value) VALUES (variable2)

END

/ /

Delimiter

Execution result:

Mysql > call p15 (0)

Query OK, 1 row affected (0.03 sec)

Mysql > select * from T2

+-+ +

| | id | value |

+-+ +

| | 1 | NULL |

+-+ +

1 row in set (0.00 sec)

Mysql > call p15 (1)

Query OK, 1 row affected (0.03 sec)

Mysql > select * from T2

+-+ +

| | id | value |

+-+ +

| | 1 | NULL |

| | 2 | 1 |

Did you pay attention to the reader? This can also run successfully. In fact, this problem can be found in the details of mysql's SQL server mode parameters.

MySQL servers can operate in different SQL modes, and different modes can be applied to different clients. In this way, each application can customize the operation mode of the server according to its own needs.

The schema defines which SQL syntax MySQL should support and what kind of data validation checks should be performed. This makes it easier to use MySQL in different environments and to use MySQL in conjunction with other servers.

You can set the default SQL mode by starting d with the-- sql-mode= "modes" option. If you want to reset, the value can also be empty (--sql-mode = "").

You can also change the SQL mode by setting the sql_mode variable with the SET [SESSION | GLOBAL] sql_mode='modes' statement after startup. Setting the GLOBAL variable requires SUPER permission and affects the operations of all clients connected since then. Setting the SESSION variable affects only the current client. Any client can change its session sql_ mode value at any time.

Modesis is a series of different patterns separated by commas (','). You can use the SELECT @ @ sql_mode statement to query the current schema. The default value is empty (no mode is set).

STRICT_TRANS_TABLES

Enable strict mode for all storage engines. Illegal data value is rejected. It is explained in detail later.

STRICT_TRANS_TABLES

Strict mode is enabled for the transactional storage engine and may also be enabled for the non-transactional storage engine. It is explained in detail later.

Strict mode controls how MySQL handles illegal or missing input values. There are several reasons to make a value illegal. For example, the data type is incorrect, does not fit the column, or is out of range. When the newly inserted row does not contain a column that does not display a value that defines the DEFAULT clause, the value is lost.

For transaction tables, an error occurs if there are illegal or missing values in the statement when STRICT_ALL_TABLES or STRICT_TRANS_TABLES mode is enabled. Statement is discarded and scrolled.

For non-transactional tables, if a bad value appears in the first row of the insert or update, the behavior of the two modes is the same. The statement is discarded and the table remains unchanged. If the statement inserts or modifies multiple rows and bad values appear in the second or later row, the result depends on which strict option is enabled:

ERROR_FOR_DIVISION_BY_ZERO

In strict mode, if divided by zero (or MOD (XP0)) during the INSERT or UPDATE process, an error (otherwise a warning) is generated. If the pattern is not given, MySQL returns NULL when divided by zero. If used in INSERT IGNORE or UPDATE IGNORE, MySQL generation is divided by zero warning, but the result is NULL.

There are other parameters that readers can refer to mysql's documentation.

Restart mysql when we add the ERROR_FOR_DIVISION_BY_ZERO parameter to sql_mode

Mysql > show variables like 'sql_mode'

+-

-- +

| | Variable_name | Value |

| |

+-

-- +

| | sql_mode | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_ |

USER,NO_ENGINE_SUBSTITUTION |

+-

-- +

1 row in set (0.00 sec)

Mysql > select 1max 0

+-+

| | 1AGO |

+-+

| | NULL |

+-+

1 row in set, 1 warning (0.00 sec)

We see 1 warning, and we take a look at this warning:

Mysql > show warnings

+-+

| | Level | Code | Message | |

+-+

| | Error | 1365 | Division by 0 | |

+-+

1 row in set (0.00 sec)

Mysql > exit

Bye

When we remove the ERROR_FOR_DIVISION_BY_ZERO parameter from sql_mode, restart mysql and try:

C:\ Documents and Settings\ Administrator > net stop mysql

The MySQL service is stopping.

The MySQL service stopped successfully.

C:\ Documents and Settings\ Administrator > net start mysql

The MySQL service has started successfully.

Mysql > select 1max 0

+-+

| | 1AGO |

+-+

| | NULL |

+-+

1 row in set (0.00 sec)

Mysql > show warnings

Empty set (0.02 sec)

At this point, the content of warnings is empty.

At this point, the reader also knows why.

The above is how the Procedure parameters of Mysql are analyzed for NULL problems. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.