How To Store Char Of Any Size Mysql

Summary: in this tutorial, you volition learn about MySQL CHAR data type and how to employ it in your database tabular array design.

Introduction to MySQL `CHAR` information type

The CHAR data blazon is a stock-still-length graphic symbol type in MySQL. You often declare the CHAR blazon with a length that specifies the maximum number of characters that you want to store. For example, CHAR(20) tin hold up to 20 characters.

If the data that you desire to shop is a fixed size, and so yous should apply the CHAR data type. You'll become a better performance in comparison with VARCHAR in this example.

The length of the CHAR data blazon tin be any value from 0 to 255. When you store a CHAR value, MySQL pads its value with spaces to the length that you lot declared.

When you query the CHAR value, MySQL removes the trailing spaces.

Note that MySQL will not remove the abaft spaces if you enable the PAD_CHAR_TO_FULL_LENGTH SQL manner.

Consider the following example.

First, creates a table with a CHAR column.

                                          CREATE                Tabular array                mysql_char_test (                status                CHAR(iii) );            
                          Code linguistic communication:              SQL (Structured Query Linguistic communication)              (              sql              )

The data type of the condition column is CHAR . And it tin concord upwardly to 3 characters.

Second, insert two rows into the mysql_char_test table.

                                          INSERT                INTO                mysql_char_test(status)                VALUES('Yes'),('No');            
                          Lawmaking linguistic communication:              SQL (Structured Query Language)              (              sql              )

Third, utilise the length function to go the length of each CHAR value.

                                          SELECT                condition,                LENGTH(status)                FROM                mysql_char_test;            
                          Code language:              SQL (Structured Query Language)              (              sql              )

Fourth, inserts a CHAR value with the leading and trailing spaces.

                                          INSERT                INTO                mysql_char_test(condition)                VALUES(' Y ');            
                          Code language:              SQL (Structured Query Linguistic communication)              (              sql              )

Finally, query the inserted values, you will see that MySQL removes the trailing spaces.

                                          SELECT                status,                LENGTH(status)                FROM                mysql_char_test;            
                          Code language:              SQL (Structured Query Language)              (              sql              )

MySQL CHAR with leading and trailing spaces

Comparing MySQL `CHAR` values

When storing or comparing the CHAR values, MySQL uses the character set collation assigned to the column.

MySQL does non consider abaft spaces when comparing CHAR values using the comparison operator such as =, <>, >, <, etc.

Find that the LIKE operator does consider the trailing spaces when you do pattern matching with CHAR values.

In the previous example, we stored the value Y with both leading and trailing spaces. All the same, when nosotros execute the following query:

                                          SELECT                *                FROM                mysql_char_test                WHERE                status                =                'Y';            
                          Code language:              SQL (Structured Query Linguistic communication)              (              sql              )

MySQL returns no row because it does not consider the abaft space. To match with the ' Y ', we demand to remove the trailing infinite as follows:

                                          SELECT                *                FROM                mysql_char_test                WHERE                condition                =                ' Y';            
                          Lawmaking language:              SQL (Structured Query Linguistic communication)              (              sql              )

MySQL `CHAR` and `UNIQUE` alphabetize

If the CHAR cavalcade has a UNIQUE index and you insert a value that is different from an existing value in a number of abaft spaces, MySQL volition decline the changes because of indistinguishable-key error.

See the following example.

Showtime, create a unique alphabetize for the condition column of the mysql_char_test tabular array.

CREATE UNIQUE Index uidx_status ON mysql_char_test(status);

Code linguistic communication: SQL (Structured Query Language) ( sql )

Second, insert a new row into the mysql_char_test tabular array.

                                          INSERT                INTO                mysql_char_test(status)                VALUES('N');            
                          Code linguistic communication:              SQL (Structured Query Language)              (              sql              )

Third, insert the post-obit value volition cause a duplicate primal mistake.

                                          INSERT                INTO                mysql_char_test(status)                VALUES('N ');            
                          Code linguistic communication:              SQL (Structured Query Language)              (              sql              )

Error Lawmaking: 1062. Duplicate entry 'N' for key 'uidx_status'

Lawmaking language: JavaScript ( javascript )

In this tutorial, we have introduced you to the MySQL CHAR data type and its features. At present, you should have a good understanding of the CHAR data type to apply information technology in your database design.

Was this tutorial helpful?