Query the database for rows containing a particular emoji expression

The case of MySQL

The code is as follows


create table tt6(id int, name varchar(800));
insert into tt6 select 1, '121😊33';
insert into tt6 select 2, 'ddd';
insert into tt6 select 3, '3669Ef';


select * from tt6

Similarly, the name field contains various strings, including strings with emoticons.

Now let's go straight to the comparison


SELECT * FROM tt6 WHERE name = '121😊33';
SELECT * FROM tt6 WHERE BINARY name = '121😊33';

In MySQL, varchar or char columns support most Unicode characters when using the utf8mb4 character set, and default comparisons are based on the character set and sorting rules. This means that you can compare strings directly using the = operator.

SELECT * FROM tt6 WHERE name = '121😊33'; Such a query compares based on the default sorting rules.

In addition, MySQL provides the BINARY keyword to convert strings to binary format for byte-by-byte comparisons.

SELECT * FROM tt6 WHERE BINARY name = '121😊33'; Such a query ignores the sorting rules and directly compares the byte values of each character.

Of course, MySQL also has an optimization here, which is that when you enter a BINARY name, you tell the optimizer to compare both sides of the equal sign in bytes, and the database automatically converts the value on the right side of the equal sign to bytes.

The case of SQL Server
The code is as follows

create table tt6(id int, name nvarchar(800));
insert into tt6 select 1, '121😊33';
insert into tt6 select 2, 'ddd';
insert into tt6 select 3, '3669Ef';

select * from tt6

In this table, the name field contains various strings, including strings with emoticons.

In SQL Server, when you compare using the nvarchar or varchar data types, the database compares and processes Unicode characters according to the column's sorting rules (collation).

This type of comparison usually takes into account the semantics of the characters.

SELECT * FROM tt6 WHERE name = '121😊33'; Such a query compares the columns according to their sorting rules.
In addition, SQL Server provides the CAST(...) AS BINARY) to convert strings to binary data for comparison, which ensures that byte-level values of strings are compared.
SELECT * FROM tt6 WHERE CAST(name AS BINARY) = '121😊33'; Such queries are compared byte-by-byte to ensure an exact match.

Of course, SQL Server has an optimization here, that is, when you enter where cast(name as binary), tell the optimizer to compare both sides of the equals sign with bytes, and the database will automatically convert the value on the right side of the equals sign to bytes, instead of requiring you to write CAST('121 😊33' as binary) on the right side of the equals sign.

summarize

In both SQL Server and MySQL, you can use both binary comparisons and direct string comparisons, because both provide different levels of comparison to meet different needs.

Basic concepts of string comparison and binary comparison

String Comparison: Typically, string comparison is based on character sets and sorting rules (collation). It takes into account the semantics of characters (e.g., case sensitivity, accent marks, etc.) when comparing characters, rather than a simple sequence of bytes.
Binary Comparison: Binary comparisons, on the other hand, are performed byte-by-byte, based solely on the actual binary value of each character. This type of comparison ignores the ordering rules of the character set and focuses only on the byte representation of the character.

reference article

/2020/02/28/sql-server-displaying-smiley-in-ssms-emoji/
/r/SQL/comments/fasio7/sql_server_supports_emoji_in_identifier_names/?rdt=40804
/blog/how-to-store-emoji-into-mysql-database#google_vignette
/Questions/5354414/How-to-store-emojis-in-a-mysql-table-Tried-everyth

This article is copyrighted and may not be reproduced without the author's permission.