MySQLバイナリ照合：パフォーマンスと文字列比較への影響を徹底解説
MySQLにおけるバイナリ照合の影響
比較とソート
バイナリ照合は、文字列をバイトシーケンスとして扱い、バイト値の比較に基づいてソートを行います。一方、非バイナリ照合は、文字列を言語に依存した規則で照合し、大文字小文字の区別、言語固有の文字の扱いなどを考慮します。
例:
-- バイナリ照合
SELECT * FROM users WHERE name = 'A𐤂B';
-- 結果: 1 件のレコードが返される
-- 非バイナリ照合 (例: utf8_general_ci)
SELECT * FROM users WHERE name = 'A𐤂B';
-- 結果: 0 件のレコードが返される
上記の例では、バイナリ照合では "A𐤂B" が "AB" と一致するためレコードが返されますが、非バイナリ照合では大文字と小文字を区別するため一致せず、レコードが返されません。
パフォーマンス
バイナリ照合は、インデックス付けや比較などの処理において、非バイナリ照合よりも高速になる場合があります。これは、バイナリ照合が文字列を単純なバイトシーケンスとして処理するため、より効率的な比較が可能となるからです。
文字セット
バイナリ照合は、文字セットに依存しません。一方、非バイナリ照合は、使用している文字セットによって照合規則が決定されます。例えば、日本語の文字列を比較する場合、utf8_japanese_ci などの日本語用の照合を使用する必要があります。
使用例
バイナリ照合は、以下のようなケースで使用されます。
- 異なる言語の文字列を比較する必要がない場合
- 画像や音声などのバイナリデータを格納する場合
- パスワードや暗号化されたデータなどのバイナリデータを格納する場合
注意事項
バイナリ照合を使用する場合は、以下の点に注意する必要があります。
- 文字セットに依存しないため、異なる文字セットのデータが混在する場合は注意が必要です。
- インデックスを作成する場合は、バイナリ照合を使用する列にインデックスを作成する必要があります。
- 大文字小文字が区別されないため、文字列比較を行う場合は注意が必要です。
This example demonstrates how binary and non-binary collations handle case-sensitive comparisons and sorting.
Create a table with a binary and non-binary column:
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
name_bin VARCHAR(255) NOT NULL COLLATE binary
);
Insert some data:
INSERT INTO users (name, name_bin) VALUES ('A𐤂B', 'A\xEF\xAC\x92B');
Query the table using binary collation:
SELECT * FROM users WHERE name_bin = 'A\xEF\xAC\x92B';
Result:
id | name | name_bin
----+------------+----------
1 | A𐤂B | A\xEF\xAC\x92B
Query the table using non-binary collation (e.g., utf8_general_ci):
SELECT * FROM users WHERE name = 'A𐤂B';
Empty set
Performance Comparison
This example compares the performance of binary and non-binary collations for sorting a large table.
Create a table with a large number of records:
CREATE TABLE large_table (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL
);
INSERT INTO large_table (name) VALUES (REPEAT('A𐤂B', 1000000));
Measure the time taken to sort the table using binary and non-binary collations:
SET TIMESTAMP = CURRENT_TIMESTAMP;
SELECT name FROM large_table ORDER BY name;
SELECT TIMESTAMP_DIFF(SECOND(), TIMESTAMP);
-- Repeat with non-binary collation (e.g., utf8_general_ci)
Compare the execution times for both cases.
Storing Binary Data
This example demonstrates how to store binary data using a binary collation.
CREATE TABLE binary_data (
id INT PRIMARY KEY AUTO_INCREMENT,
data BLOB NOT NULL COLLATE binary
);
Insert binary data:
INSERT INTO binary_data (data) VALUES (0x48656C6C6F20776F726C6421);
Retrieve the binary data:
SELECT data FROM binary_data WHERE id = 1;
0x48656C6C6F20776F726C6421
This code demonstrates that binary collation can be used to store and retrieve binary data efficiently.
For case-insensitive comparisons without using binary collations, you can utilize collation functions like LOWER() or UPPER() to convert the strings to a uniform case before comparison.
SELECT * FROM users WHERE LOWER(name) = LOWER('a𐤂b');
Custom Character Set Comparison
If you need more granular control over string comparisons, you can create a custom character set or collation that defines the desired comparison rules. This involves defining a comparison table that maps characters to their collation weights.
Application-Level Handling
In cases where the need for binary-like comparisons is limited to specific operations, you can handle the string comparisons at the application layer. This involves converting the strings to binary representations and performing byte-level comparisons within the application code.
Alternative Data Storage
For storing purely binary data that doesn't require character-based comparisons, consider using data types like BLOB or VARBINARY without specifying a collation. These data types store raw binary data without any character interpretation.
Textual Representation for Display
When displaying binary data for human consumption, you can convert it to a suitable text representation using encoding schemes like HEX() or BASE64. This allows for displaying the binary data in a human-readable format.
mysql database binary
