2019年4月28日

验证 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
[
\
]
^
_
`
{
|
}
~

You may also like...

发表评论

电子邮件地址不会被公开。 必填项已用*标注


*