Mysql Procedure parameter is NULL problem analysis

I recently discovered an interesting thing when writing a procedure. Mysql procedure has its own unique way of handling some "illegal" parameters when passing parameters. For example, if a parameter originally defined as int is passed in as null
, the MySQL procedure 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 procedure:
delimiter //
CREATE PROCEDURE p14 (IN parameter1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = parameter1 + 1;
INSERT INTO t(value) VALUES (variable1);
END;
//
delimiter ;

Operating results:


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 passed equal to 5, 6 is inserted into the table and the data is normal.
When parameter parameter1 is passed in as null, NULL is inserted into the table. Why is this?

Regarding this point, you can look at the statement that declares variables. The document gives this 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 there is no DEFAULT clause, the initial value is NULL.

A new question arises from the above: NULL=NULL+1? Haha, it’s a bit interesting. What is the reasonable explanation for SET variable1 = parameter1 + 1; at this time?

This is the explanation given by Teacher Wang (the second one is very classic~~~):
1 null+1=null
Because null behaves like a "pointer", that is, it points to the "content of address 0", if this If the content is "null", it will behave as null. That's why specifying INT is also empty. However, if the "content" has a value, it will not be empty. For MYSQL, it is a "random number" or 0; when the content of this address is stored, the value is fixed;


2 If A=B+1 , A is NULL only when B is null; SET A=B+1, can it be understood as SET (B+1), A has been replaced in the 'current', so it does not matter who A is, what is important is B+ 1;
This idea has not been verified, mainly because SET cannot be separated, and the documentation of mysql5 supports this statement, but the English version uses "replacement" instead of the Chinese "set" expression. It feels that the meaning is closer! (SET)

A new question: Can it run successfully when A=1/B and 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 |

Did readers notice? This also works successfully. In fact, this problem can be found in the SQL server mode parameter details of mysql.
MySQL server can operate in different SQL modes, and different modes can be applied to different clients. This way each application can customize the server's operating mode 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 with other database servers.
You can start mysqld with the --sql-mode="modes" option to set the default SQL mode. This value can also be left empty (--sql-mode = "") if you want to reset it.
You can also use the SET [SESSION|GLOBAL] sql_mode='modes' statement to set the sql_mode variable to change the SQL mode after startup. Setting a GLOBAL variable requires SUPER permission and affects the operation of all clients connected from that point on. Setting the SESSION variable only affects the current client. Any client can change its session sql_mode value at any time.
Modesis is a series of different modes separated by commas (‘,’). You can query the current mode using the SELECT @@sql_mode statement. The default value is empty (no mode is set).
STRICT_TRANS_TABLES
Enable strict mode for all storage engines. Illegal data values ​​are rejected. Detailed instructions follow.
· STRICT_TRANS_TABLES
Enables strict mode for transactional storage engines, and possibly for non-transactional storage engines. Detailed instructions follow.
Strict mode controls how MySQL handles illegal or missing input values. There are several reasons why a value may be illegal. For example, the data type is wrong, does not fit in the column, or is out of range. When a newly inserted row does not contain a value for a column that does not explicitly define a DEFAULT clause, the value is lost.
For transaction tables, when STRICT_ALL_TABLES or STRICT_TRANS_TABLES mode is enabled, an error will occur if there are illegal or missing values ​​in the statement. The statement is abandoned and rolled.
For non-transactional tables, both modes behave the same if a bad value occurs in row 1 of an insert or update. The statement is discarded and the table remains unchanged. If a statement inserts or modifies multiple rows, and the bad value occurs in the 2nd or later row, the result depends on which strict option is enabled:
ERROR_FOR_DIVISION_BY_ZERO
In strict mode, during an INSERT or UPDATE, if divide by zero (or MOD (X, 0)), an error occurs (otherwise a warning). If the mode is not given, MySQL returns NULL when dividing by zero. If used in INSERT IGNORE or UPDATE IGNORE, MySQL generates a divide-by-zero warning, but the operation result is NULL.
There are also other parameters, readers can refer to the mysql documentation.

When we add the ERROR_FOR_DIVISION_BY_ZERO parameter to sql_mode, restart mysql
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 1/0;
+-- ----+
| 1/0 |
+------+
| NULL |
+------+
1 row in set, 1 warning (0.00 sec)

We see it 1 warning, let’s 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 SettingsAdministrator>net stop mysql
MySQL service is stopping.
MySQL service has been stopped successfully.


C:Documents and SettingsAdministrator>net start mysql

The MySQL service has been started successfully.


mysql> select 1/0;
+------+
| 1/0 |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql> show warnings;
Empty set (0.02 sec)
The content of warnings is empty at this time.
At this point, readers also know why. When you have time, try other databases and see if the same is true