CODEWITHSUNDEEP
mysqlunicodesql-insertsymbols
SHEKHAR SHETE
SHEKHAR SHETE

Reputation: 6066

Incorrect string value: '\xF0\x9F\x92\x8F\x0A#...for column in mysql?

I am new to MySQL. I have a column which stores Unicode character in MySQL table.

I have a code which gets the posts from the twitter,facebook etc. and insert the text/symbol 'as is' into the table.

CREATE TABLE `tbl_unicode` (
  `_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `uninoce_Column` varchar(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`_id`)

) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8;


/* This query WONT work due to SYMBOL:💏   */
INSERT INTO `tbl_unicode`
  (`_id`,
  `first_name`,
  `uninoce_Column`)
VALUES
  (2,
  'rakesh',
  '最高の休日💏
#京都#宮津#天の橋立#');


/* This query WORKS FINE */
INSERT INTO `tbl_unicode`
  (`_id`,
  `first_name`,
  `uninoce_Column`)
VALUES
  (1,
  'shekhar',
  '最高の休日
#京都#宮津#天の橋立#');
enter code here

Live Demo in SQLFiddle

So, which datatype or configuration should i do to make it work.

Any suggestions?

Upvotes: 3

Views: 3509

Answers (2)

bobince
bobince

Reputation: 536597

CHARACTER SET utf8

MySQL's utf8 charset is not UTF-8. That would be too easy! Instead, it's a limited subset of UTF-8 where only code points up to U+00FFFF, which can be stored in three UTF-8 bytes, are supported. 💏 is U+01F48F, so it doesn't fit in the table's charset.

The real UTF-8 character set is known in MySQL (5.5 and later) as utf8mb4.

Upvotes: 6

David
David

Reputation: 1

Use nvarchar as datatype for your table fields. varchar only stores 8-bit characters while nvarchar also stores unicode characters.

Upvotes: -1

Related Questions