How to deal with the sql_mode problem in MySQL stored procedure

This article mainly introduces how to deal with the sql_mode problem in MySQL stored procedures, which is very detailed and has a certain reference value. Friends who are interested must read it!

Sql_mode='STRICT_TRANS_TABLES' is set in my.cnf

However, it is found that illegal data is still written. For example, the original value of the unsigned integer field is 0 and can be subtracted. Of course, the result is overflowed. After checking, it is found that the default sql_mode in the stored procedure does not use global's sql_mode. The test is as follows:

CREATE PROCEDURE p_test ()

BEGIN

Select @ @ session.sql_mode,@@global.sql_mode

END

Call p_test ()

+-+ +

| | @ @ session.sql_mode | @ @ global.sql_mode |

+-+ +

| | STRICT_TRANS_TABLES |

+-+ +

1 row in set (0.00 sec)

CREATE PROCEDURE p_test1 ()

BEGIN

Set sql_mode=@@global.sql_mode

Select @ @ session.sql_mode,@@global.sql_mode

END

Call p_test1 ()

+-+ +

| | @ @ session.sql_mode | @ @ global.sql_mode |

+-+ +

| | STRICT_TRANS_TABLES | STRICT_TRANS_TABLES |

+-+ +

1 row in set (0.00 sec)

These are all the contents of the article "how to deal with sql_mode problems in MySQL stored procedures". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!