mysql变量赋值错误的是_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 |

+---------+

1 row in set (0.00 sec)