照合順序

• Oracle

(12cR2)
https://qiita.com/plusultra/items/1a5aff16e017d12417fb

※COLLATE や DEFAULT COLLATION を使用するためには
MAX_STRING_SIZEを EXTENDEDに設定する必要がある

show parameter MAX_STRING_SIZE;
shutdown immediate;
startup upgrade;
alter system set MAX_STRING_SIZE=EXTENDED;
@?/rdbms/admin/utl32k.sql
shutdown immediate;
startup
show parameter MAX_STRING_SIZE;

1.データベースレベル

drop table tab1 purge;

create table tab1(col1 int, col2 varchar2(10), col3 varchar2(10));

insert into tab1 values(1,'A','A');
insert into tab1 values(2,'A','a');
insert into tab1 values(3,'A','Ａ');
insert into tab1 values(4,'A','ａ');
commit;
select * from tab1 order by col1;

--デフォルト設定の場合

show parameter NLS_COMP;
show parameter NLS_SORT;

select * from tab1 where col2 = col3;

→大文字小文字を区別する
→全角半角を区別する

--大文字小文字や全角半角を区別なしに変更の場合

ALTER SESSION SET NLS_COMP='LINGUISTIC';
ALTER SESSION SET NLS_SORT='JAPANESE_M_CI';

show parameter NLS_COMP;
show parameter NLS_SORT;

select * from tab1 where col2 = col3;

2.テーブルレベル

drop table tab1 purge;

create table tab1(col1 int, col2 varchar2(10), col3 varchar2(10)) default COLLATION JAPANESE_M_CI;

insert into tab1 values(1,'A','A');
insert into tab1 values(2,'A','a');
insert into tab1 values(3,'A','Ａ');
insert into tab1 values(4,'A','ａ');
commit;
select * from tab1 order by col1;

select * from tab1 where col2 = col3;

3.カラムレベル

drop table tab1 purge;

create table tab1(col1 int, col2 varchar2(10) COLLATE JAPANESE_M_CI, col3 varchar2(10) COLLATE JAPANESE_M_CI);

insert into tab1 values(1,'A','A');
insert into tab1 values(2,'A','a');
insert into tab1 values(3,'A','Ａ');
insert into tab1 values(4,'A','ａ');
commit;
select * from tab1 order by col1;

select * from tab1 where col2 = col3;

• MySQL

https://qiita.com/tfunato/items/e48ad0a37b8244a788f6

(5.6)

※前提
vim /etc/my.cnf

[mysqld]
character-set-server = utf8mb4

--照合順序一覧
show collation;

1.データベースレベル

--デフォルト設定の場合

use mysql
drop database test;
create database test;
use test

create table tab1(col1 int, col2 varchar(10), col3 varchar(10));

insert into tab1 values(1,'A','A');
insert into tab1 values(2,'A','a');
insert into tab1 values(3,'A','Ａ');
insert into tab1 values(4,'A','ａ');

select * from tab1 order by col1;

SELECT @@collation_database;
show table status like 'tab1'\G
show full columns from tab1\G

select * from tab1 where col2 = col3;

→大文字小文字を区別しない
→全角半角を区別する

--大文字小文字や全角半角を区別するに変更の場合

use mysql
drop database test;
create database test COLLATE utf8mb4_bin;
use test

create table tab1(col1 int, col2 varchar(10), col3 varchar(10));

insert into tab1 values(1,'A','A');
insert into tab1 values(2,'A','a');
insert into tab1 values(3,'A','Ａ');
insert into tab1 values(4,'A','ａ');

select * from tab1 order by col1;

SELECT @@collation_database;
show table status like 'tab1'\G
show full columns from tab1\G

select * from tab1 where col2 = col3;

2.テーブルレベル

--大文字小文字や全角半角を区別するに変更の場合

use mysql
drop database test;
create database test;
use test

create table tab1(col1 int, col2 varchar(10), col3 varchar(10)) COLLATE utf8mb4_bin;

insert into tab1 values(1,'A','A');
insert into tab1 values(2,'A','a');
insert into tab1 values(3,'A','Ａ');
insert into tab1 values(4,'A','ａ');

select * from tab1 order by col1;

SELECT @@collation_database;
show table status like 'tab1'\G
show full columns from tab1\G

select * from tab1 where col2 = col3;

3.カラムレベル

--大文字小文字や全角半角を区別するに変更の場合

use mysql
drop database test;
create database test;
use test

create table tab1(col1 int, col2 varchar(10) COLLATE utf8mb4_bin, col3 varchar(10) COLLATE utf8mb4_bin);

insert into tab1 values(1,'A','A');
insert into tab1 values(2,'A','a');
insert into tab1 values(3,'A','Ａ');
insert into tab1 values(4,'A','ａ');

select * from tab1 order by col1;

SELECT @@collation_database;
show table status like 'tab1'\G
show full columns from tab1\G

select * from tab1 where col2 = col3;

• PostgreSQL

(9.4)

1.データベースレベル

--デフォルト設定の場合

\c postgres
drop database test;
create database test;
\c test

create table tab1(col1 int, col2 varchar(10), col3 varchar(10));

insert into tab1 values(1,'A','A');
insert into tab1 values(2,'A','a');
insert into tab1 values(3,'A','Ａ');
insert into tab1 values(4,'A','ａ');

select * from tab1 order by col1;

\l

select * from tab1 where col2 = col3;

→大文字小文字を区別する
→全角半角を区別する

--大文字小文字や全角半角を区別しないに変更の場合

→LC_COLLATEやLC_CTYPEでは設定できない模様

2.テーブルレベル

→LC_COLLATEやLC_CTYPEの設定がテーブルレベルでできない模様

3.カラムレベル

--大文字小文字や全角半角を区別しないに変更の場合

→LC_COLLATEやLC_CTYPEでは設定できない模様

• SQL Server

https://docs.microsoft.com/ja-jp/sql/relational-databases/collations/collation-and-unicode-support?redirectedfrom=MSDN&view=sql-server-ver15
https://blogs.msdn.microsoft.com/jpsql/2016/07/26/1-3/

(2014)

--照合順序一覧
SELECT * FROM sys.fn_helpcollations() WHERE [name] NOT LIKE N'SQL%';

1.データベースレベル

--デフォルト設定の場合

use master
drop database test;
create database test;
use test

create table tab1(col1 int, col2 varchar(10), col3 varchar(10));

insert into tab1 values(1,'A','A');
insert into tab1 values(2,'A','a');
insert into tab1 values(3,'A','Ａ');
insert into tab1 values(4,'A','ａ');

select * from tab1 order by col1;

select name, collation_name
from sys.databases
where name = 'test';

select table_name,column_name,collation_name
from [test].[INFORMATION_SCHEMA].[COLUMNS]
where table_catalog = 'test'
order by table_name,column_name;

select * from tab1 where col2 = col3;

→大文字小文字を区別しない
→全角半角を区別しない

--大文字小文字や全角半角を区別するに変更の場合

use master
drop database test;
create database test COLLATE Japanese_BIN2;
use test

create table tab1(col1 int, col2 varchar(10), col3 varchar(10));

insert into tab1 values(1,'A','A');
insert into tab1 values(2,'A','a');
insert into tab1 values(3,'A','Ａ');
insert into tab1 values(4,'A','ａ');

select * from tab1 order by col1;

select name, collation_name
from sys.databases
where name = 'test';

select table_name,column_name,collation_name
from [test].[INFORMATION_SCHEMA].[COLUMNS]
where table_catalog = 'test'
order by table_name,column_name;

select * from tab1 where col2 = col3;

2.テーブルレベル

→COLLATEの設定がテーブルレベルでできない模様

3.カラムレベル

--大文字小文字や全角半角を区別するに変更の場合

use master
drop database test;
create database test;
use test

create table tab1(col1 int, col2 varchar(10) COLLATE Japanese_BIN2, col3 varchar(10) COLLATE Japanese_BIN2);

insert into tab1 values(1,'A','A');
insert into tab1 values(2,'A','a');
insert into tab1 values(3,'A','Ａ');
insert into tab1 values(4,'A','ａ');

select * from tab1 order by col1;

select name, collation_name
from sys.databases
where name = 'test';

select table_name,column_name,collation_name
from [test].[INFORMATION_SCHEMA].[COLUMNS]
where table_catalog = 'test'
order by table_name,column_name;

select * from tab1 where col2 = col3;