Mysql给变量赋值两种方法

• set @name=value 或 set @name:=value

mysql> select * from shop;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 1 | A | 3.45 |
| 1 | B | 3.99 |
| 2 | A | 10.99 |
| 3 | B | 1.45 |
| 3 | C | 1.69 |
| 3 | D | 1.25 |
| 4 | D | 19.95 |
+---------+--------+-------+
7 rows in set (0.00 sec)

mysql> set @name='D';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from shop where dealer=@name;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 3 | D | 1.25 |
| 4 | D | 19.95 |
+---------+--------+-------+
2 rows in set (0.00 sec)

mysql> set @name:=10.99;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from shop where price=@name;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 2 | A | 10.99 |
+---------+--------+-------+
1 row in set (0.00 sec)

mysql> select @name:=3.99;
+-------------+
| @name:=3.99 |
+-------------+
| 3.99 |
+-------------+
1 row in set (0.00 sec)

mysql> select * from shop where price=@name;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 1 | B | 3.99 |
+---------+--------+-------+
1 row in set (0.00 sec)

• select @name:=value

mysql> select @name:=max(price) from shop;
+-------------------+
| @name:=max(price) |
+-------------------+
| 19.95 |
+-------------------+
1 row in set (0.00 sec)

mysql> select * from shop where price=@name;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 4 | D | 19.95 |
+---------+--------+-------+
1 row in set (0.00 sec)