Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
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