
概述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