MYSQL 动态变量赋值不对的情况
那天我碰到了个怪问题。今天解决了问题。
不过具体原因还有待查找。
在WINDOWS下正常。
DROP TABLE IF EXISTS `t`;
CREATE TABLE `t` (
`id` int(11) NOT NULL auto_increment,
`g_id` int(11) NOT NULL,
`t_str` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
key (`g_id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;
/*Data for the table `t` */
insert into `t`(`id`,`g_id`,`t_str`) values
(1,2,'wo'),
(2,2,'ni'),
(3,2,'ta'),
(4,3,'wo '),
(5,4,'ni'),
(6,3,'ni'),
(7,4,'ta'),
(8,3,'wang'),
(9,4,'li'),
(10,3,'hai'),
(11,4,'ri'),
(12,2,'ren'),
(13,5,'ta'),
(14,6,'ri'),
(15,6,'ren'),
(16,6,'fuck'),
(17,6,'shit'),
(18,5,'ls'),
(19,5,'chmod'),
(20,5,'chgrp'),
(21,5,'chown'),
(22,3,'rm'),
(23,3,'desc'),
(24,4,'pwd'),
(25,5,'cd');
错误输出
mysql> select g_id from t group by g_id order by g_id desc limit 0,1;
+------+
| g_id |
+------+
| 6 |
+------+
1 row in set (0.00 sec)
mysql> select g_id from t group by g_id order by g_id desc limit 0,1 into @tmp_id;
Query OK, 1 row affected (0.00 sec)
mysql> select @tmp_id;
+---------+
| @tmp_id |
+---------+
| 5 |
+---------+
1 row in set (0.00 sec)
mysql> select version();
+-----------------+
| version() |
+-----------------+
| 5.1.21-beta-log |
+-----------------+
1 row in set (0.00 sec)
mysql>
正确输出：
mysql> select @tmp_id := g_id from t group by g_id order by g_id desc limit 0,1 into @tmp_id;
Query OK, 1 row affected (0.00 sec)
mysql> select @tmp_id;
+---------+
| @tmp_id |
+---------+
| 6 |
+---------+
不过具体原因还有待查找。
在WINDOWS下正常。
DROP TABLE IF EXISTS `t`;
CREATE TABLE `t` (
`id` int(11) NOT NULL auto_increment,
`g_id` int(11) NOT NULL,
`t_str` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
key (`g_id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;
/*Data for the table `t` */
insert into `t`(`id`,`g_id`,`t_str`) values
(1,2,'wo'),
(2,2,'ni'),
(3,2,'ta'),
(4,3,'wo '),
(5,4,'ni'),
(6,3,'ni'),
(7,4,'ta'),
(8,3,'wang'),
(9,4,'li'),
(10,3,'hai'),
(11,4,'ri'),
(12,2,'ren'),
(13,5,'ta'),
(14,6,'ri'),
(15,6,'ren'),
(16,6,'fuck'),
(17,6,'shit'),
(18,5,'ls'),
(19,5,'chmod'),
(20,5,'chgrp'),
(21,5,'chown'),
(22,3,'rm'),
(23,3,'desc'),
(24,4,'pwd'),
(25,5,'cd');
错误输出
mysql> select g_id from t group by g_id order by g_id desc limit 0,1;
+------+
| g_id |
+------+
| 6 |
+------+
1 row in set (0.00 sec)
mysql> select g_id from t group by g_id order by g_id desc limit 0,1 into @tmp_id;
Query OK, 1 row affected (0.00 sec)
mysql> select @tmp_id;
+---------+
| @tmp_id |
+---------+
| 5 |
+---------+
1 row in set (0.00 sec)
mysql> select version();
+-----------------+
| version() |
+-----------------+
| 5.1.21-beta-log |
+-----------------+
1 row in set (0.00 sec)
mysql>
正确输出：
mysql> select @tmp_id := g_id from t group by g_id order by g_id desc limit 0,1 into @tmp_id;
Query OK, 1 row affected (0.00 sec)
mysql> select @tmp_id;
+---------+
| @tmp_id |
+---------+
| 6 |
+---------+
1 row in set (0.00 sec)
本文转自 david_yeung 51CTO博客，原文链接：http://blog.51cto.com/yueliangdao0608/81267，如需转载请自行联系原作者