mysql 存储过程 prepare into_mysql 存储过程项目小结

最新推荐文章于 2022-09-19 11:06:32 发布

DoomDinse

最新推荐文章于 2022-09-19 11:06:32 发布

阅读量120

点赞数

CC 4.0 BY-SA版权

文章标签： mysql 存储过程 prepare into

本文链接：https://blog.youkuaiyun.com/weixin_42620202/article/details/113596251

本文介绍了MySQL中真假值的表示方法及如何使用IF函数进行判断，并演示了如何通过存储过程执行动态SQL，包括创建预处理语句、执行及释放预处理语句的过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1. false ：0 true 1 切记

官方文档：http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html

These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true:

mysql> SELECT IF(0, 'true', 'false');+------------------------+

| IF(0, 'true', 'false') |

+------------------------+

| false |

+------------------------+

mysql> SELECT IF(1, 'true', 'false');+------------------------+

| IF(1, 'true', 'false') |

+------------------------+

| true |

+------------------------+

mysql> SELECT IF(2, 'true', 'false');+------------------------+

| IF(2, 'true', 'false') |

+------------------------+

| true |

+------------------------+

However, the values TRUE and FALSE are merely aliases for 1 and 0, respectively, as shown here:

mysql> SELECT IF(0 = FALSE, 'true', 'false');+--------------------------------+

| IF(0 = FALSE, 'true', 'false') |

+--------------------------------+

| true |

+--------------------------------+

mysql> SELECT IF(1 = TRUE, 'true', 'false');+-------------------------------+

| IF(1 = TRUE, 'true', 'false') |

+-------------------------------+

| true |

+-------------------------------+

mysql> SELECT IF(2 = TRUE, 'true', 'false');+-------------------------------+

| IF(2 = TRUE, 'true', 'false') |

+-------------------------------+

| false |

+-------------------------------+

mysql> SELECT IF(2 = FALSE, 'true', 'false');+--------------------------------+

| IF(2 = FALSE, 'true', 'false') |

+--------------------------------+

| false |

+--------------------------------+

The last two statements display the results shown because 2 is equal to neither 1 nor 0.

2 存储过程中执行动态sql

官方文档：

http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html

The first example shows how to create a prepared statement by using a string literal to supply the text of the statement:

mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';

mysql> SET @a = 3;

mysql> SET @b = 4;

mysql> EXECUTE stmt1 USING @a, @b;+------------+

| hypotenuse |

+------------+

| 5 |

+------------+

mysql> DEALLOCATE PREPARE stmt1;

The second example is similar, but supplies the text of the statement as a user variable:

mysql> SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';

mysql> PREPARE stmt2 FROM @s;

mysql> SET @a = 6;

mysql> SET @b = 8;

mysql> EXECUTE stmt2 USING @a, @b;+------------+

| hypotenuse |

+------------+

| 10 |

+------------+

mysql> DEALLOCATE PREPARE stmt2;

Here is an additional example which demonstrates how to choose the table on which to perform a query at runtime, by storing the name of the table as a user variable:

mysql> USEtest;

mysql> CREATE TABLE t1 (a INT NOT NULL);

mysql> INSERT INTO t1 VALUES (4), (8), (11), (32), (80);

mysql> SET @table = 't1';

mysql> SET @s = CONCAT('SELECT * FROM', @table);

mysql> PREPARE stmt3 FROM @s;

mysql> EXECUTEstmt3;+----+

| a |

+----+

| 4 |

| 8 |

| 11 |

| 32 |

| 80 |

+----+

mysql> DEALLOCATE PREPARE stmt3;

总结：

执行动态sql，分三步走：

1. PREPARE；

Syntax：

PREPARE stmt_name FROM preparable_stmt

2. EXECUTE；

Syntax：

EXECUTE stmt_name[USING @var_name [, @var_name] ...]

3. DEALLOCATE PREPARE；

Syntax：

{DEALLOCATE | DROP} PREPARE stmt_name

3. 存储过程中的事务

语法：

START TRANSACTION [WITH CONSISTENT SNAPSHOT]

BEGIN [WORK]

COMMIT [WORK] [AND [NO] CHAIN] [[NO]RELEASE]ROLLBACK [WORK] [AND [NO] CHAIN] [[NO]RELEASE]SET autocommit = {0 | 1}