Skip to content

Instantly share code, notes, and snippets.

@kamipo
Last active August 21, 2018 05:51
Show Gist options
  • Star 2 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save kamipo/37576ce436c564d8cc28 to your computer and use it in GitHub Desktop.
Save kamipo/37576ce436c564d8cc28 to your computer and use it in GitHub Desktop.
Sushi-Beer issue of MySQL with utf8mb4
SET NAMES utf8mb4;
DROP TABLE IF EXISTS wishlist_unicode_520;
CREATE TABLE wishlist_unicode_520 (
item varchar(1)
) COLLATE utf8mb4_unicode_520_ci;
DROP TABLE IF EXISTS wishlist_bin;
CREATE TABLE wishlist_bin (
item varchar(1)
) COLLATE utf8mb4_bin;
INSERT INTO wishlist_unicode_520 VALUES ('🍣'), ('🍺');
INSERT INTO wishlist_bin VALUES ('🍣'), ('🍺');
SELECT item, HEX(WEIGHT_STRING(item)) FROM wishlist_unicode_520 WHERE item = '🍣';
SELECT item, HEX(WEIGHT_STRING(item)) FROM wishlist_unicode_520;
SELECT item, HEX(WEIGHT_STRING(item)) FROM wishlist_bin WHERE item = '🍣';
SELECT item, HEX(WEIGHT_STRING(item)) FROM wishlist_bin;
SET NAMES utf8mb4;
DROP TABLE IF EXISTS wishlist_general;
CREATE TABLE wishlist_general (
item varchar(1)
) COLLATE utf8mb4_general_ci;
DROP TABLE IF EXISTS wishlist_unicode;
CREATE TABLE wishlist_unicode (
item varchar(1)
) COLLATE utf8mb4_unicode_ci;
INSERT INTO wishlist_general VALUES ('🍣'), ('🍺');
INSERT INTO wishlist_unicode VALUES ('🍣'), ('🍺');
SELECT item, HEX(WEIGHT_STRING(item)) FROM wishlist_general WHERE item = '🍣';
SELECT item, HEX(WEIGHT_STRING(item)) FROM wishlist_unicode WHERE item = '🍣';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment