Why CHAR and VARCHAR return the same length in MySQL?

Question

According to this answer, CHAR and VARCHAR return the length differently.

But when I run this code:

CREATE TABLE EMPLOYEE
( ID            INT,
  Var_Name      VARCHAR(5) NOT NULL,
  Char_Name     CHAR(5)    NOT NULL,
  Age           INT,
  Dnumber       INT,
  Level         INT,
  PRIMARY KEY (ID)
);

INSERT INTO EMPLOYEE (ID, Var_Name, Char_Name, Age, Dnumber, Level) VALUES(1,'Tom', 'Tom', 21, 2, 5);
INSERT INTO EMPLOYEE (ID, Var_Name, Char_Name, Age, Dnumber, Level) VALUES(2,'Lucky', 'Lucky', 22, 2, 3);
INSERT INTO EMPLOYEE (ID, Var_Name, Char_Name, Age, Dnumber, Level) VALUES(3,'Blue', 'Blue', 20, 2, 3);

SELECT Var_Name, LENGTH(Var_Name), LENGTH(Char_Name) FROM EMPLOYEE

Output:

Var_Name    LENGTH(Var_Name)    LENGTH(Char_Name)
Tom         3                   3
Lucky       5                   5
Blue        4                   4

I think the output should like this:

Var_Name    LENGTH(Var_Name)    LENGTH(Char_Name)
Tom         3                   5
Lucky       5                   5
Blue        4                   5

Why CHAR and VARCHAR return the same length?

score 4 · Accepted Answer · answered Nov 10 '20 at 04:56

MySQL 8.0 Reference Manual / ... / The CHAR and VARCHAR Types

The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255. When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed unless the PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled.

fiddle

Why CHAR and VARCHAR return the same length in MySQL?

1 Answers1