What mean by char(40)?-CodePudding

I have a mysql table which has a data structure as follows,

create table data(
....
name char(40) NULL,

...
)

But I could insert names which has characters more than 40 in to name field. Can someone explain what is the actual meaning of char(40)?

CodePudding user response：

You cannot insert a string of more than 40 characters in a column defined with the type CHAR(40).

If you run MySQL in strict mode, you will get an error if you try to insert a longer string.

mysql> create table mytable ( c char(40) );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into mytable (c) values ('Now is the time for all good men to come to the aid of their country.');
ERROR 1406 (22001): Data too long for column 'c' at row 1

If you run MySQL in non-strict mode, the insert will succeed, but only the first 40 characters of your string is stored in the column. The characters beyond 40 are lost, and you get no error.

mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into mytable (c) values ('Now is the time for all good men to come to the aid of their country.');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warnings;
 --------- ------ ---------------------------------------- 
| Level   | Code | Message                                |
 --------- ------ ---------------------------------------- 
| Warning | 1265 | Data truncated for column 'c' at row 1 |
 --------- ------ ---------------------------------------- 
1 row in set (0.00 sec)

mysql> select c from mytable;
 ------------------------------------------ 
| c                                        |
 ------------------------------------------ 
| Now is the time for all good men to come |
 ------------------------------------------ 
1 row in set (0.00 sec)

I recommend operating MySQL in strict mode (strict mode is the default since MySQL 5.7). I would prefer to get an error instead of losing data.

CodePudding user response：

the CHAR(size) has Maximum size of 255 characters. MySQL CHAR() function returns the character value of the given integer value according to the ASCII table. It ignores the NULL value. here 40 in ascii means '(' using ibm docs

CodePudding user response：

char [ ( n ) ] Fixed-size string data.n defines the string size in bytes and must be a value from 1 through 8,000. For single-byte encoding character sets such as 'Latin', the storage size is n bytes and the number of characters that can be stored is also n. For multibyte encoding character sets, the storage size is still n bytes but the number of characters that can be stored may be smaller than n. The ISO synonym for char is character.

Links for more info: -[1]: https://docs.microsoft.com/en-us/sql/t-sql/data-types/char-and-varchar-transact-sql?view=sql-server-ver15