mysql 存储过程项目小结

mysql 存储过程项目小结,第1张

概述1. false :0  true 1 切记官方文档:http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html BOOL, BOOLEANThese 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.htmlThe 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> USE test;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> EXECUTE stmt3;+----+| a |+----+| 4 || 8 || 11 || 32 || 80 |+----+mysql> DEALLOCATE PREPARE stmt3; 总结:执行动态sql,分三步走:1. PREPARE;Syntax:PREPARE stmt_name FROM preparable_stmt2. 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} 

1. false :0  true 1 切记

官方文档:http://dev.MysqL.com/doc/refman/5.0/en/numeric-type-overvIEw.HTML

 , 

. A value of zero is consIDered false. Nonzero values are consIDered true:

MysqL (,, (,) false (, (,) true (, (,) true

 and  are merely aliases for  and ,respectively,as shown here:

MysqL ( FALSE, ( FALSE,) true ( TRUE, ( TRUE,) true ( TRUE, ( TRUE,) false ( FALSE, ( FALSE,) false

 is equal to neither  nor .

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 stmt1 stmt1 USING , hypotenuse MysqL stmt1;

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

MysqL stmt2 stmt2 USING , hypotenuse MysqL 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 t1 (a t1 (),(),(),(),(MysqL<span >> <span >SET <span >@table <span >= <span >'<span >t1<span >'<span >;
MysqL
<span >>
<span >SET
<span >@s
<span >=
CONCAT(<span >'
<span >SELECT * FROM
<span >'
,<span >@table
<span >);

MysqL<span >> <span >PREPARE stmt3 <span >FROM <span >@s<span >;
MysqL<span >> <span >EXECUTE<span > stmt3;
<span >+<span >--<span >--+
<span >| a <span >|
<span >+<span >--<span >--+
<span >| <span >4 <span >|
<span >| <span >8 <span >|
<span >| <span >11 <span >|
<span >| <span >32 <span >|
<span >| <span >80 <span >|
<span >+<span >--<span >--+
<span >
MysqL<span >> <span >DEALLOCATE <span >PREPARE stmt3;

总结:

执行动态sql,分三步走:

1. PREPARE;

 Syntax:

stmt_name FROM preparable_stmt

2. EXECUTE;

   Syntax:

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

3. DEALLOCATE PREPARE;

  Syntax:

stmt_name

 3. 存储过程中的事务

 语法:

START CHAIN] CHAIN] autocommit { } 总结

以上是内存溢出为你收集整理的mysql 存储过程项目小结全部内容,希望文章能够帮你解决mysql 存储过程项目小结所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错,欢迎将内存溢出网站推荐给程序员好友。

欢迎分享,转载请注明来源:内存溢出

原文地址: http://outofmemory.cn/sjk/1169146.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2022-06-02
下一篇 2022-06-02

发表评论

登录后才能评论

评论列表(0条)

保存