最近一直使用语句，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.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) 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.01 sec)
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.00 sec) 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.00 sec) 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'生效了~