Tekencodering (MySQL)

The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

Multibyte characters

Ooit had je alleen ASCII en vergelijkbare eenvoudige tekenset-coderingen. ASCII is van oorsprong een 7-bit codering en kent dus 127 verschillende waardes. Da's voldoende voor hoofdletters, kleine letters, cijfers, een handjevol symbolen en enkele control characters, maar daarna houdt het al snel op. Zelfs met 8-bit ASCII-codering kom je niet erg ver.

Uitgebreidere systemen, zoals UTF-x, zijn zodoende multibyte: Voor een groot aantal karakters wordt meer dan één byte gebruikt. '€' kost in utf meestal drie bytes en '𝔊' vergt zelfs vier bytes. Wellicht dat je dat kunt terugzien aan de bijbehorende URL escape code: %F0%9D%94%8A. Merk op dat deze systemen een variabele byte-lengte gebruiken. Dat geldt (geloof ik) weer niet voor bv. utf16 of utf32.

Voorbeeld

select 

   # length(_utf7 "1"),	        # Not recognised
   length(_utf8 "1"),		# 1
   length(_utf16 "1"),		# 2
   length(_latin1 "1"),	        # 1
    
    # length(_utf7 "€"),	# Not recognised
    length(_utf8 "€"),		# 3
    length(_utf16 "€"),		# 4
    length(_latin1 "€");	# 1

Multibyte safe

Met multibyte safe wordt volgens mij bedoeld, dat berekeningen of uitkomsten niet in de war worden geschopt doordat de betreffende karakters multibyte zijn. Je hoeft er dus geen rekening mee te houden dat je met multibyte-karakters werkt.

Voorbeeld: De karakterlengte van '1' en '€' is steeds 1, ook al is '€' hoogstwaarschijn een multibyte-karakter (hangt van de codering af).

Tekencoderingen

utf8mb3 - 1-3 bytes/char
utf8mb4 - 1-4 bytes/char
utf8 → Alias voor utf8mb3
ucs2 - 2 bytes/char
utf16 - 2-4 bytes/char
utf16le - Als utf16, maar little-endian
utf32 - 4 bytes/char

Daarnaast kan een tekencodering uitgebreid worden met specificaties van de collation (sorteervolgorde ofzo) Bv.:

utf8_general
utf8_ci
utf8_danish_ci

Vergeet wat hierboven staat: Je kunt het met één commando in MySQL tevoorschijn toveren:

mysql> show character set;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset           | binary              |      1 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
+----------+---------------------------------+---------------------+--------+

Binaire strings

het utf8-teken "1" weergeven in binair, geeft in MySQL Workbench alleen het label BLOB. Als ik daarna kies voor Open Value in Viewer, krijg ik dit. Dit is hexadecimaal, maar da's goed genoeg voor dit moment

Ik wil tekenreeksen afkappen op byte-niveau. Een gebruikelijke manier lijkt te zijn, door de betreffende string eerst om te zetten naar binair.

Binaire (hexadecimale) representatie van het cijfer "1"

set @01=convert("1" using utf8);
select convert(@01 using binary);

>> 31 (hexadecimaal)

Representatie van een tekenreeks + left-functie

set @01=convert("123" using utf8);
set @02=convert(@01 using binary);

select @02;
select left(@02,2);

>> 31 32 33
>> 31 32

ű in utf8 & latin1

UTF8:

select convert("ű" using binary);

>> c5 b1

Latin1:

select convert(convert("ű" using latin1) using binary);

>> 3f

utf8 is de standaard-codering

De hexadecimale representatie van karakters, is afhankelijk van de gebruikte standaard-codering: MySQL gebruikt niet een of andere universele codering ofzo. Indien de codering niet is gegeven, wordt utf8 gebruikt. Dat staat vast ergens in my.ini ofzo.

select convert("ű" using binary);
select convert(convert("ű" using utf8) using binary);

>> c5 b1   # Zonder expliciet utf8 te noemen, wordt-ie gebruikt
>> c5 b1   # Expliciet utf8 gebruikt

€ in utf8 & latin-1

UTF8:

set @01=convert("€" using utf8);
set @02=convert(@01 using binary);

select @02;

>> e2 82 ac

set @01=convert("€" using latin1);
set @02=convert(@01 using binary);

select @02;

>> 80

Mutibyte-karakters afbreken

set @01=convert("€123" using utf8);
set @02=convert(@01 using binary);
select convert(left(@02,3) using utf8) as uitkomst;

>> €

set @01=convert("123€" using utf8);
set @02=convert(@01 using binary);
select convert(left(@02,3) using utf8) as uitkomst;

>> 123

set @01=convert("€123" using utf8);
set @02=convert(@01 using binary);
select convert(left(@02,2) using utf8) as uitkomst;

>> NULL # MySQL weigert een multibyte-karakter halverwege af te breken

Binaire left-functie

Tjakka:

CREATE DEFINER=`root`@`localhost` FUNCTION `left_byte`
(
   input_string text,
   input_position integer
) 
RETURNS text CHARSET utf8
BEGIN

# Byte-wise left function
################################################################################
#
# * multibyte-safe for characters of up to 4 bytes (=max. utf8)
# * utf8 Assumed to be the general encoding
# * Strompf, July 2018

return 
ifnull(
   ifnull(
      ifnull(
         convert(left(convert(input_string using binary), input_position) using utf8),
         convert(left(convert(input_string using binary), input_position-1) using utf8)
      ),convert(left(convert(input_string using binary), input_position-2) using utf8)
   ),convert(left(convert(input_string using binary), input_position-3) using utf8)
);    
END