Storage of Unicode Characters in MySql and Oracle

Question

Scenario: A database in MySql and Oracle. Both have same table and same columns. In one of the table I have a Column, 'FullName'.

Oracle Datatype used is VarChar2(64). MySql Datatype used is VarChar(64).

To Support Unicode Character UTF 8 is used as the default character set.

I'm getting a error in Oracle, which says "value too large for column", where as in MySql it works fine.

I would like to know, what's the difference between these two dbases?

Note: For japanese Character in UTF 8 encoding take 3 bytes.

Thanks in Advance.

Pavan

In Oracle, is the column declared as `VARCHAR2(64 BYTE)` or `VARCHAR2(64 CHAR)`? — Jeffrey Kemp, Mar 01 '11 at 07:53
CREATE TABLE "DUMMYUSER" ( . . . "FULL_NAME" VARCHAR2(64), . . . ) ; — Pavan Navali, Mar 01 '11 at 09:30
Thanks to Jeffery Kemp for asking this question. Gave me a completely new perspective to look a the problem...found the solution to it. — Pavan Navali, Mar 01 '11 at 12:12

score 2 · Accepted Answer · edited May 23 '17 at 12:11

The reason it was working in MYSQL is because VarChar(64) in MYSQL means, 64 characters and the default character is UTF8.

For oracle it was not working, although the default Character Set for Oracle was UTF8. Reason is VarChar2(64), means 64 bytes. And For japanese Character in UTF 8 encoding take 3 bytes for a single character. Hence the error "value too large for column".

Thank to David Sykes for the solution in the following link.

Difference between BYTE and CHAR in column datatypes

Storage of Unicode Characters in MySql and Oracle

1 Answers1