MySQL DB - saving a special character ★

Question

1. I have a table in MYSQL. It is defined as character set - utf8, collation: utf8_general_ci. The ITEM_TITLE column defined as a varchar.

2. I have this query:
   UPDATE details SET ITEM_TITLE="★32GB VALUE PACK★ Sony Xperia miro ST23i 4GB Int White with silver" WHERE TaskID=109

3. The result is that in the column ITEM_TITLE i have a title without the ★ and with "?" instead.

THE QUESTION: What should i do, in order to save "★" in the DB properly?

**I found a solution for SQL server - using Nvarchar and not varchar but there is no such type in MySQL.

Answer: I understood what was the problem and why the solution that worked for someone, didnt work for me. First off all, the default for my DB is charset latin. So i changed it to UTF-8 but it didnt solve the issue. The reason for that is that i had a charset latin for most of the rows. I didnt know that it is set automatically for varchar and doesnt change even when i set the table as UTF-8. So I changed everything to UTF-8 and it works fix.

Thanks for all the help!

Answer 1

There shouldn't be a problem with that. Let's try it out

mysql> CREATE TABLE details (
    ->   id int(11) unsigned NOT NULL AUTO_INCREMENT,
    ->   item_title varchar(255) DEFAULT NULL,
    ->   PRIMARY KEY (id)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO details (item_title) VALUES('★32GB VALUE PACK★ Sony Xperia miro ST23i 4GB Int');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM details;
+----+------------------------------------------------------+
| id | item_title                                           |
+----+------------------------------------------------------+
|  1 | ★32GB VALUE PACK★ Sony Xperia miro ST23i 4GB Int     |
+----+------------------------------------------------------+
1 row in set (0.00 sec)

Therefore you most likely break these characters on the way in/out to/from the database.