Whats the differences between CHAR, TEXT data types in mysql database

Question

0.00/5 (No votes)

See more:

I know this is a silly question, but I understand from that link

MySQL :: MySQL 8.0 Reference Manual :: 10.10.1 Unicode Character Sets[^]

MySQL :: MySQL 8.0 Reference Manual :: 11.7 Data Type Storage Requirements[^]

that CHAR support wide character EG: Unicode, Utf8, etc.
But TEXT is only string support only AnsiC.

If so...

Its inconsistency with C language Data Type concept which indicate CHAR as AnsiC string.

how to understand Mysql theorizm ?

What I have tried:

I have tried Input text as utf8 and also it returned utf8.

Posted 30-Mar-20 0:50am

Member 11010948

Updated 31-Mar-20 20:26pm

v3

Add a Solution

Comments

KarstenK 30-Mar-20 8:07am

Be very careful, best is NOT to use TEXT. This avoids bugs when some text operations are using the database.

OriginalGriff is absolut correct.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2020-03-30T01:15:00

ANSI C uses the ASCII charact6ers set - which is a 7 bit code set, with a limited range of characters available. These map exactly into the "bottom 128" of the Unicode characters set, so storing ASCII in Unicode is trivial. But going the other way ...

Unicode has more characters available - it's effectively a 16 bit codeset - and UTF8 stores most characters in a single 8 bit byte, which does fit into a CHAR as its' actually stored as an 8 bit value, not 7 bits. But ... once you move outside the "basic" character sets, it becomes a problem, and the number of bytes needed to store a UTF character is not fixed at one (as ASCII is) - it's flexible, so it can store wider Unicode values in a series of 8 bit characters.

So while you can specify "ten characters" in TEXT fields, and get 10 ASCII characters in there every time, you can't necessarily store 10 Unicode characters in there, even if they are UTF8 as the number of bytes needed to store the ten characters can be between ten and twenty depending on the actual data you store.

CHAR fields allocate 16 bits per character, so the whole Unicode character set can always be stored.

If in doubt, always use CHAR rather than TEXT.