mysql prepare语法_Mysql prepare 语法

最近一直使用语句，SELECT auction_id, auction_name,SUM(new_cart),SUM(new_collect),SUM(total_cart),SUM(total_collect)  FROM tableName WHERE seller_id = ? AND thedate >= ? AND thedate <= ?  GROUP BY auction_id LIMIT ?, ? 不知道是什么意思，原来是Mysql的prepare的应用，防止脚本注入实用的，下面记录一个事例：

1、set @session_uid='120189386';set @day1='2013-10-10';set @day2='2013-10-10';set @offset='0'; set @limit='10';

Query OK, 0 rows affected (0.00sec)

Query OK, 0 rows affected (0.00sec)

Query OK, 0 rows affected (0.00sec)

Query OK, 0 rows affected (0.00sec)

Query OK, 0 rows affected (0.00 sec)

2、prepare s1 from 'SELECT auction_id, auction_name,SUM(new_cart),SUM(new_collect),SUM(total_cart),SUM(total_collect)  FROM rpt_fmp_eleven_auction_info_d_01 WHERE seller_id = ? AND thedate >= ? AND thedate <= ?  GROUP BY auction_id LIMIT ?, ?';

Query OK, 0 rows affected (0.01sec)

Statement prepared

3、execute s1 using @session_uid,@day1,@day2,@offset,@limit;

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

| auction_id | auction_name | SUM(new_cart) | SUM(new_collect) | SUM(total_cart) | SUM(total_collect) |

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

| 123 | ?? | 1 | 2 | 3 | 4 |

| 1234 | ?? | 1 | 2 | 3 | 4 |

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

2 rows in set (0.00 sec)

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

特别提醒：prepare对IN支持的不好，所以遇到这样的情况只能自己写程序。比如：

mysql> select id,auction_id from rpt_fmp_eleven_auction_info_d_00 where auction_id in (111110,12);+----+------------+

| id | auction_id |

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

| 2 | 12 |

| 1 | 111110 |

| 1 | 111110 |

| 1 | 111110 |

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

4 rows in set (0.00 sec)

记住有4条记录，奇迹是：

mysql> set @auctions='111110,12';

Query OK,0 rows affected (0.00sec)

mysql> prepare s1 from 'select id,auction_id from rpt_fmp_eleven_auction_info_d_00 where auction_id in(?)';

Query OK,0 rows affected (0.00sec)

Statement prepared

mysql> execute s1 using@auctions;+----+------------+

| id | auction_id |

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

| 1 | 111110 |

| 1 | 111110 |

| 1 | 111110 |

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

3 rows in set (0.00 sec)

只有3条了，说明只有set @aucitons='111110,12'的第一条记录'111110'生效了~