{"id":4987,"date":"2013-01-21T23:02:39","date_gmt":"2013-01-22T06:02:39","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=4987"},"modified":"2024-01-04T20:12:52","modified_gmt":"2024-01-05T03:12:52","slug":"mysql-char-data-type","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-char-data-type\/","title":{"rendered":"MySQL CHAR Data Type"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about MySQL CHAR<\/code> data type and how to apply it in your database table design.<\/p>\n\n\n\n

Introduction to MySQL CHAR data type<\/h2>\n\n\n\n

The CHAR<\/code> data type is a fixed-length character type in MySQL. You often declare the CHAR<\/code> type with a length that specifies the maximum number of characters that you want to store. For example, CHAR(20)<\/code> can hold up to 20 characters.<\/p>\n\n\n\n

If the data that you want to store is a fixed size, then you should use the CHAR<\/code> data type. You’ll get a better performance in comparison with VARCHAR<\/code> this case.<\/p>\n\n\n\n

The length of the CHAR<\/code> data type can be any value from 0 to 255. When you store a CHAR<\/code> value, MySQL pads its value with spaces to the length that you declared.<\/p>\n\n\n\n

When you query the CHAR<\/code> value, MySQL removes the trailing spaces.<\/p>\n\n\n\n

Note that MySQL will not remove the trailing spaces if you enable the PAD_CHAR_TO_FULL_LENGTH<\/a> SQL mode.<\/p>\n\n\n\n

Consider the following example.<\/p>\n\n\n\n

First, create a table<\/a> with a CHAR<\/code> column.<\/p>\n\n\n

CREATE<\/span> TABLE<\/span> mysql_char_test (\n    status<\/span> CHAR<\/span>(3<\/span>)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The data type of the\u00a0 status<\/code> column is CHAR<\/code> . It can hold up to 3 characters.<\/p>\n\n\n\n
INSERT<\/span> INTO<\/span> mysql_char_test(status<\/span>)\nVALUES<\/span>('Yes'<\/span>),('No'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span> \n    status<\/span>, \n    LENGTH<\/span>(status<\/span>)\nFROM<\/span>\n    mysql_char_test;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
Fourth, insert a CHAR<\/code> value with the leading and trailing spaces.<\/p>\n\n\n
INSERT<\/span> INTO<\/span> mysql_char_test(status<\/span>)\nVALUES<\/span>(' Y '<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, query the inserted values, and you will see that MySQL removes the trailing spaces.<\/p>\n\n\n
SELECT<\/span> \n    status<\/span>, \n    LENGTH<\/span>(status<\/span>)\nFROM<\/span>\n    mysql_char_test;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
Comparing MySQL CHAR values<\/h2>\n\n\n\n
When storing or comparing the CHAR<\/code> values, MySQL uses the character set<\/a> collation<\/a> assigned to the column.<\/p>\n\n\n\n
MySQL does not consider trailing spaces when comparing CHAR<\/code> values using the comparison operator such as =, <>, >, <, etc.<\/p>\n\n\n\n
Notice that the LIKE<\/a><\/code> operator does consider the trailing spaces when you do pattern matching with CHAR<\/code> values.<\/p>\n\n\n\n
In the previous example, we stored the value Y<\/code> with both leading and trailing spaces. However, when we execute the following query:<\/p>\n\n\n
SELECT<\/span> * \nFROM<\/span> mysql_char_test\nWHERE<\/span> status<\/span> = 'Y'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL returns no row because it does not consider the trailing space. To match with the ‘ Y ‘, we need to remove the trailing space as follows:<\/p>\n\n\n
SELECT<\/span> *\nFROM<\/span> mysql_char_test\nWHERE<\/span> status<\/span> = ' Y'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
MySQL CHAR and UNIQUE index<\/h2>\n\n\n\n
If the CHAR<\/code> column has a UNIQUE<\/a><\/code> index and you insert a value that is different from an existing value in a number of trailing spaces, MySQL will reject the changes because of duplicate-key error.<\/p>\n\n\n\n
See the following example.<\/p>\n\n\n\n
First, create a unique index<\/a> for the status<\/code> column of the mysql_char_test<\/code> table.<\/p>\n\n\n
CREATE<\/span> UNIQUE<\/span> INDEX<\/span> uidx_status \nON<\/span> mysql_char_test(status<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span> mysql_char_test(status<\/span>)\nVALUES<\/span>('N'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, insert the following value will cause a duplicate key error.<\/p>\n\n\n
INSERT<\/span> INTO<\/span> mysql_char_test(status<\/span>)\nVALUES<\/span>('N '<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n
Error<\/span> Code: 1062.<\/span> Duplicate entry 'N'<\/span> for<\/span> key 'uidx_status'<\/span><\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • MySQL CHAR<\/code> data type is a fixed-length character type.<\/li>\n\n\n\n
  • Use the CHAR<\/code> data type to store fixed-length character data.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Second, insert a new row<\/a> into the mysql_char_test<\/code> table.<\/p>\n\n\n
Third, use the length<\/a> function to get the length of each CHAR<\/code> value.<\/p>\n\n\n
Second, insert two rows<\/a> into the mysql_char_test<\/code> table.<\/p>\n\n\n