1. false ：0  true 1 切记

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

•  BOOL, BOOLEAN

  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> 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_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}

mysql 存储过程项目小结