验证 SQL 的排序规则

问题

在 SQL 中，各个字符的排序规则是什么？哪个在前，哪个在后？例如，我想知道下面这些字符在 SQL 中的顺序，怎么办？

0123ABCDabcd!@#$%^&*()-_+=[]{};:'"\|`~,.<>/?

解决方案

思路：在 MySQL 中创建一张表，只存一列数据，格式为 CHAR(1)，然后依次插入上面的字符，再用 SELECT ORDER BY 来查看顺序。SQL 语句为：

CREATE TABLE char_test
(
  foochar CHAR(1) NOT NULL
);
INSERT INTO char_test(foochar) VALUES('0');
INSERT INTO char_test(foochar) VALUES('1');
INSERT INTO char_test(foochar) VALUES('2');
INSERT INTO char_test(foochar) VALUES('3');
INSERT INTO char_test(foochar) VALUES('A');
INSERT INTO char_test(foochar) VALUES('B');
INSERT INTO char_test(foochar) VALUES('C');
INSERT INTO char_test(foochar) VALUES('D');
INSERT INTO char_test(foochar) VALUES('a');
INSERT INTO char_test(foochar) VALUES('b');
INSERT INTO char_test(foochar) VALUES('c');
INSERT INTO char_test(foochar) VALUES('d');
INSERT INTO char_test(foochar) VALUES('!');
INSERT INTO char_test(foochar) VALUES('@');
INSERT INTO char_test(foochar) VALUES('#');
INSERT INTO char_test(foochar) VALUES('$');
INSERT INTO char_test(foochar) VALUES('%');
INSERT INTO char_test(foochar) VALUES('^');
INSERT INTO char_test(foochar) VALUES('&');
INSERT INTO char_test(foochar) VALUES('*');
INSERT INTO char_test(foochar) VALUES('(');
INSERT INTO char_test(foochar) VALUES(')');
INSERT INTO char_test(foochar) VALUES('-');
INSERT INTO char_test(foochar) VALUES('_');
INSERT INTO char_test(foochar) VALUES('+');
INSERT INTO char_test(foochar) VALUES('=');
INSERT INTO char_test(foochar) VALUES('[');
INSERT INTO char_test(foochar) VALUES(']');
INSERT INTO char_test(foochar) VALUES('{');
INSERT INTO char_test(foochar) VALUES('}');
INSERT INTO char_test(foochar) VALUES(';');
INSERT INTO char_test(foochar) VALUES(':');
INSERT INTO char_test(foochar) VALUES('\'');
INSERT INTO char_test(foochar) VALUES('"');
INSERT INTO char_test(foochar) VALUES('\\');
INSERT INTO char_test(foochar) VALUES('|');
INSERT INTO char_test(foochar) VALUES('`');
INSERT INTO char_test(foochar) VALUES('~');
INSERT INTO char_test(foochar) VALUES(',');
INSERT INTO char_test(foochar) VALUES('.');
INSERT INTO char_test(foochar) VALUES('<');
INSERT INTO char_test(foochar) VALUES('>');
INSERT INTO char_test(foochar) VALUES('/');
INSERT INTO char_test(foochar) VALUES('?');

SELECT foochar FROM char_test ORDER BY foochar;
复制

执行结果如下：

! " # $ % & ' ( ) * + , - . / 0 1 2 3 : ; < = > ? @ a A b B c C d D [ \ ] ^ _ ` { | } ~
复制