# collation differences experiment
## docker run both services
### mysql
- mysql container
```=bash
docker run --name mysql-server -p 3306:3306 -e MYSQL_ROOT_PASSWORD=your_password -d mysql:8.0
docker exec -it mysql-server bash
mysql -u root -p
```
- create test_db and test_table
```=bash
CREATE DATABASE test_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE test_db;
CREATE TABLE test_table (test_column VARCHAR(255));
```
### postgresql
- postgresql container
```=bash
docker run --name postgres-server -p 5432:5432 -e POSTGRES_PASSWORD=your_password -d postgres:13.7
docker exec -it postgres-server bash
psql -U postgres
```
- create test_db and test_table
```=bash
CREATE DATABASE test_db WITH ENCODING 'UTF8' LC_COLLATE='C.UTF-8' LC_CTYPE='C.UTF-8';
\c test_db
CREATE TABLE test_table (test_column VARCHAR(255));
```
## Test cases
if postgresql: collation_name=C.UTF-8
if mysql: collation_name=utf8mb4_unicode_ci
### 1. Alphabet Sorting
```=sql
INSERT INTO test_table (test_column) VALUES ('a'), ('A'), ('b'), ('B'), ('C'), ('ç'), ('Đ'), ('e');
SELECT * FROM test_table ORDER BY test_column COLLATE "collation_name";
```
- outputs
- mysql（case insensitive所以把a跟A放一起，e放最後）
```
+-------------+
| test_column |
+-------------+
| |
| |
| a |
| A |
| b |
| B |
| C |
| e |
+-------------+
8 rows in set (0.00 sec)
```
- postgresql（case sensitive所以把大寫字元照順序放前面，接續小寫，再來是特殊字元）
```
test_column
-------------
A
B
C
a
b
e
ç
Đ
(8 rows)
```
### 2. special character sorting
|Char|ASCII|Unicode|
|---|---|---|
|+|43|U+002B|
|$|36|U+0024|
|-|45|U+002D|
|!|33|U+0021|
|@|64|U+0040|
|#|35|U+0023|
```=sql
TRUNCATE TABLE test_table;
INSERT INTO test_table (test_column) VALUES ('+'), ('$'), ('-'), ('!'), ('@'), ('#');
SELECT * FROM test_table ORDER BY test_column COLLATE "collation_name";
```
- outputs
- mysql
```
+-------------+
| test_column |
+-------------+
| - |
| ! |
| @ |
| # |
| + |
| $ |
+-------------+
6 rows in set (0.00 sec)
```
- postgresql (using ascii)
```
test_column
-------------
!
#
$
+
-
@
(6 rows)
```
### 3. Accent Mark Sorting
```=sql
TRUNCATE TABLE test_table;
INSERT INTO test_table (test_column) VALUES ('résumé'), ('resume'), ('resumé');
SELECT * FROM test_table ORDER BY test_column COLLATE "collation_name";
```
- outputs
- mysql（辨識不出accent mark）
```
+-------------+
| test_column |
+-------------+
| resum |
| resume |
| rsum |
+-------------+
3 rows in set (0.01 sec)
```
- postgresql （可以辨識出accent mark）
```
test_column
-------------
resume
resumé
résumé
(3 rows)
```
### 4. Case-Sensitive Search
```=sql
TRUNCATE TABLE test_table;
INSERT INTO test_table (test_column) VALUES ('Hello'), ('HELLO'), ('HeLLo');
SELECT * FROM test_table WHERE test_column = 'hello' COLLATE "collation_name";
```
- outputs
- <font style color = 'red'>**mysql**</font>
```
ERROR 1253 (42000): COLLATION 'utf8mb4_unicode_ci' is not valid for CHARACTER SET 'latin1'
```
- 這邊也會報一樣的錯
```=sql
INSERT INTO test_table (test_column) VALUES ('Yeah'),('YEAH'), ('YeAH');
SELECT * FROM test_table WHERE test_column = 'yeah' COLLATE "utf8mb4_unicode_ci";
```
- postgresql
```
test_column
-------------
(0 rows)
```
- <font style color='blue'>use ILIKE</font>
```=sql
SELECT * FROM test_table WHERE test_column ilike 'hello';
```
```
test_column
-------------
Hello
HELLO
HeLLo
(3 rows)
```
### 5. Accent-Sensitive Search
```=sql
TRUNCATE TABLE test_table;
INSERT INTO test_table (test_column) VALUES ('résumé'), ('resume'), ('resumé');
SELECT * FROM test_table WHERE test_column = 'resume' COLLATE "collation_name";
```
- outputs
- mysql
```
ERROR 1253 (42000): COLLATION 'utf8mb4_unicode_ci' is not valid for CHARACTER SET 'latin1'
```
- postgresql
```
test_column
-------------
resume
(1 row)
```
- use ilike
```=sql
SELECT * FROM test_table WHERE test_column ilike 'resume' COLLATE "C.UTF-8";
```
```
test_column
-------------
resume
(1 row)
```
Sign in with Wallet
Connect another wallet