How to store Emoji Character in MySQL Database
1) Database: Change Database default collation as utf8mb4
.
2) Table: Change table collation as CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
.
Query:
ALTER TABLE Tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
3) Code:
INSERT INTO tablename (column1, column2, column3, column4, column5, column6, column7)
VALUES ('273', '3', 'Hdhdhdhððððhzhzhzzhjzj æç±ä½ â', 49, 1, '2016-09-13 08:02:29', '2016-09-13 08:02:29')
4) Set utf8mb4
in database connection:
$database_connection = new mysqli($server, $user, $password, $database_name);
$database_connection->set_charset('utf8mb4');
Step 1, change your database's default charset:
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
if the db is not created yet, create it with correct encodings:
CREATE DATABASE database_name DEFAULT CHARSET = utf8mb4 DEFAULT COLLATE = utf8mb4_unicode_ci;
Step 2, set charset when creating table:
CREATE TABLE IF NOT EXISTS table_name (
...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;
or alter table
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE table_name MODIFY field_name TEXT CHARSET utf8mb4;