MySQL 通过变量设置整理

Question

为了避免在不同系统上更新数据库时出现“混合非法集合”，我想这样做:

    SELECT @collcation := TABLE_COLLATION FROM information_schema.tables WHERE table_schema = "information_schema" AND TABLE_NAME = "COLUMNS";
    SELECT * FROM ... WHERE COLUMN_NAME="extended" COLLATE @collcation;

是否可以从变量设置 COLLATE？

Answer 1

这里是我的例子:

select @collcation := TABLE_COLLATION
from information_schema.tables
where table_schema='testdrive_unit_test' and TABLE_NAME = "car"
;
+--------------------------------+
| @collcation := TABLE_COLLATION |
+--------------------------------+
| latin1_swedish_ci              |
+--------------------------------+

select * from car order by model collate @collcation
;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@collcation' at line 1

select * from car order by model collate latin1_swedish_ci limit 3
;
+----+----------+-------+----------+
| id | brand_id | model | maxSpeed |
+----+----------+-------+----------+
| 14 |        5 | 301   |      160 |
| 15 |        5 | 401   |      190 |
| 16 |        5 | 407   |      210 |
+----+----------+-------+----------+
3 rows in set (0.00 sec)

在这个观点中，我认为不可能从变量设置排序规则。