Error while inserting the special character black diamond (�​) in the MySQL / MariaDB

While executing the SQL insert operation, you will get the error as follows:

create table test_specialchar (name varchar(500));
insert into test_specialchar values ('�​');

SQL Error [1366] [22007]: (conn=27) Incorrect string value: '\xEF\xBF\xBD\xE2\x80\x8B' 
for column `classicmodels`.`test_specialchar`.`name` at row 1

Cause: The character set on which table or column has does not support the insertion of this special character.

Solution: Change the character set to utf8mb4 it will support 4 bytes of data which will support more special characters. In MySQL/MariaDB, you can change character set at table or column level.

-- For Column level:
ALTER TABLE test_specialchar MODIFY name varchar(500) CHARACTER SET utf8mb4;

--For table level:
ALTER TABLE test_specialchar CONVERT TO CHARACTER SET utf8mb4;

It will fix the issue.

Solution of error “SQL Error [1366] [22007]: (conn=27) Incorrect string value: ‘\xEF\xBF\xBD\” as an example shown below:

-- Create a table
create table test_specialchar (name varchar(500))

-- Try to insert special character into the table:
insert into test_specialchar values ('�​')

-- Error
SQL Error [1366] [22007]: (conn=27) Incorrect string value: '\xEF\xBF\xBD\xE2\x80\x8B' 
for column `classicmodels`.`test_specialchar`.`name` at row 1

-- Check the character set for the table:
select table_name,table_collation from information_Schema.tables where table_name = 'test_specialchar';
table_name      |table_collation  |
----------------+-----------------+
test_specialchar|latin1_swedish_ci|

-- Check character set for column:
select column_name,character_Set_name,collation_name from information_Schema.columns where table_name = 'test_specialchar';

column_name|character_Set_name|collation_name   |
-----------+------------------+-----------------+
name       |latin1            |latin1_swedish_ci|

-- Change the character set for column to UTF8Mb4
ALTER TABLE test_specialchar MODIFY name varchar(500) CHARACTER SET utf8mb4;

--Insert the special character again:
insert into test_specialchar values ('�​');	

The insertion of special char is done successfully.