Mysql Procedure parameter is NULL problem analysis _ MySQL

Mysql Procedure parameter is NULL problem analysis bitsCN.com

An interesting thing was found during the recent writing process. Mysql procedure has its own unique processing method when passing parameters. For example, if the parameter is defined as an int, the result is null.
Mysql procedure runs normally.
Database 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 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)


No. when parameter1 is set to 5, Table 6 is inserted and data is normal.
When parameter1 is input as null, the table inserts NULL. why.

For more information, see the statements for declaring Variables. The document provides the following explanation: the declare statement is used to declare local variables. To provide a DEFAULT value for a variable, include a DEFAULT clause. The value can be specified as an expression and does not need to be a constant. If no DEFAULT clause exists, the initial value is NULL.

The above introduces another question: NULL = NULL + 1? Haha, it's a bit interesting. how can we reasonably explain the current SET variable1 = parameter1 + 1?

This is what Mr. Wang explained (the second one is classic ~~~) :
1 null + 1 = null
Null indicates a pointer, that is, the content pointing to the "0 address". if the content is "null", it indicates null. This is why the specified INT is 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 and B is null, A is NULL. set a = B + 1 can be interpreted as SET (B + 1 ), A has been replaced by the 'current ', so who is A is unimportant and what is important is B + 1;
This idea has not been verified, mainly because the SET cannot be separated, while the mysql5 documentation supports this statement. However, the English version uses "replace" instead of "SET" in Chinese, I feel more similar! (SET)

A new question: can it run successfully when A = 1/B, B = 0?

Mysql>

Delimiter //
Create procedure p15 (IN parameter1 INT)
BEGIN
Declare variable2 float (5, 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 |

Do you notice? This operation can also be successful. In fact, this problem can be found in the details of mysql SQL server mode parameters.
The MySQL server can operate in different SQL modes, and can apply different modes to different clients. In this way, each application can customize the server operation mode as needed.
The mode defines which SQL syntaxes should be supported by MySQL and which data verification checks should be performed. This makes it easier to use MySQL in different environments and use it with other database servers.
You can use the -- SQL-mode = "modes" option to start mysqld to set the default SQL mode. If you want to reset, this value can also be blank (-- SQL-mode = "").
You can also use the SET [SESSION | GLOBAL] SQL _mode = 'modes 'statement to SET the SQL _mode variable after startup to change the SQL mode. The SUPER permission is required when setting GLOBAL variables, and operations on all clients connected from that time will be affected. Setting SESSION variables only affects the current client. Any client can change its own session SQL _mode value at any time.
Modesis is a series of different modes separated by commas. You can use the SELECT @ SQL _mode statement to query the current mode. The default value is null (no mode is set ).
STRICT_TRANS_TABLES
Enable strict mode for all storage engines. The invalid data value is denied. Detailed descriptions are provided later.
· 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 a transaction table, 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 the statement inserts or modifies multiple rows and the bad value appears in the 2nd or later rows, the result depends on which strict option is enabled:
ERROR_FOR_pISION_BY_ZERO
In strict mode, if the INSERT or UPDATE process 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.
There are also some other parameters. you can refer to the mysql documentation.

When the ERROR_FOR_pISION_BY_ZERO parameter is added to SQL _mode, restart mysql.
Mysql> show variables like 'SQL _ mode ';
+ --------------- + ---------------------------------------------------------------
---------------------------- +
| Variable_name | Value
|
+ --------------- + ---------------------------------------------------------------
---------------------------- +
| SQL _mode | STRICT_TRANS_TABLES, ERROR_FOR_pISION_BY_ZERO, NO_AUTO_CREATE _
USER, NO_ENGINE_SUBSTITUTION |
+ --------------- + ---------------------------------------------------------------
---------------------------- +
1 row in set (0.00 sec)

Mysql & gt; select 1/0;
+ ------ +
| 1, 1/0 |
+ ------ +
| NULL |
+ ------ +
1 row in set, 1 warning (0.00 sec)

We see 1 warning. we are looking 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_pISION_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 has been stopped successfully.


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

The MySQL service has been started successfully.


Mysql & gt; select 1/0;
+ ------ +
| 1, 1/0 |
+ ------ +
| NULL |
+ ------ +
1 row in set (0.00 sec)

Mysql> show warnings;
Empty set (0.02 sec)
The content of warnings is empty.
Now, readers know why. Try other databases if you have time.

This article is from the boring pp blog

BitsCN.com