0

As I said, I found a strange phenomenon when referring to other people's projects,

some people's table columns (qualified for the title) use char instead of tinyint, such as

create table A(
  id int not null auto_increment,
  a_seq char(9) comment 'The first one is 1, 2, 3 (national, private, foreign)'
  a_type char(1) comment '0 is normal, 1 is disable'
  a_status char(1) comment '0 is visible, 1 is not'
)

For a_type and a_status, both char(1) and tinyint(1) are one byte, and the comparison speed of numeric characters and numbers may be as fast, so the difference between the two is not very big.

So for a_seq, why not use int for storage? int only takes up 4 bytes, but char(9) takes up 9 bytes. If you add a UNIQUE index to a_seq, doesn't char(9) take up space and be slow?

To add, I also saw someone store the year (2020, 2021) in char(4) instead of shortint

Can anyone tell me the reason for this empirically, as I'm getting confused by this.

Improve this question

2 Answers 2

Reset to default
0

Why sometimes choose to use char instead of int

A half of access libraries/frameworks provides parameter values as strings unconditionally. In this case the server compares the column and literal as strings, without any type convertion.

the comparison speed of numeric characters and numbers may be as fast

I doubt. If one value to be compared is numeric and another one is string then both values to be compared are converted to floating point (double precision) values. See Type Conversion in Expression Evaluation.

If you add a UNIQUE index to a_seq, doesn't char(9) take up space and be slow?

More disk space? of course. Slow? it depends.

Improve this answer
7
  • Sorry for some ambiguity in my question, for THE SECOND My intention is that in the case of char(1) and tinyint(1), number-to-number comparisons may be as fast as character-to-character comparisons And for THE THIRD I searched for the difference in query rate between int and char as the primary key, and in the case of equal footprint (such as char(4) vs int), the difference in query rate between them is very small But when unequal, the smaller win)
    – Ice_Wift
    Apr 8 at 6:38
  • When I designed a table once, there was a field that was the actual ID of the entity, denoted as a_seq, which is different from the auto-incrementing primary key id. It is 19 digits long! But my partner ended up using char(19) and indexing it The source of my doubts is it, why give up the bigint of 8 bytes and use the char of 19 bytes instead?
    – Ice_Wift
    Apr 8 at 6:40
  • @Ice_Wift It is 19 digits long! BIGINT UNSIGNED may store this value. why give up the bigint of 8 bytes and use the char of 19 bytes instead? I cannot find any reason except library/framework specificity mentioned above. number-to-number comparisons may be as fast as character-to-character comparisons The difference in comparing time for numeric values and for short strings makes no sense in most cases. This time difference is too small while comparing with the time needded for another query execution steps.
    – Akina
    Apr 8 at 7:18
  • 1
    @Ice_Wift In addition to what Akina said about it possibly being a constraint of the consumer framework outside the database, also sometimes some developers don't know any better and make poor design decisions by choosing improper data types. Or if the data is loaded from an external source or multiple external sources, some developers use string-based data types to simplify the Load process in their ELT workflow by not worrying about the actual type that could even change, from the external source that they have no control over.
    – J.D.
    Apr 8 at 12:01
  • @Akina - Wrong (or incomplete) on one point. WHERE int_col = "123" converts the literal to int.
    – Rick James
    Apr 8 at 19:40
0

INT takes 4 bytes. CHAR(9) takes at least 9 bytes. If you are putting a number in CHAR(9), then you can get up to 1 billion. INT can handle a bigger number than that.

Significantly, putting numbers in CHAR (or VARCHAR) make them compare 'incorrectly' when using an inequality test:

 2  <  10    -- numeric comparison
"2" > "10"   -- because "2" > "1"

Which do you need?

There is a datatype called YEAR; wny not use that?

VARCHAR stores only the characters needed; CHAR pads to the length given. Don't use CHAR unless you really need the padding.

There are similar arguments for using DATETIME instead of VARCHAR.

In general, use the appropriate datatype!

As for speed, ... fetching the rows involved is far more costly than something as trivial as comparing one but with another. The time difference is probably so insignificant as to be essentially impossible to measure.

The default collation in the MySQL 8.0 is a complex utf8mb4 (UTF-8) collation. Even for a simple equality test, it must check each byte in a complete way. A simple example is "B" = "b" -- that is case folding. Add accented letters or non-spacing accents and it becomes much more complex.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.