MySQL Tutorial/Data Types/Char Varchar
Содержание
- 1 All CHAR and VARCHAR values in MySQL are compared without regard to any trailing spaces
- 2 CHAR and VARCHAR
- 3 Differences between CHAR and VARCHAR
- 4 If a given value is stored into the CHAR(4) and VARCHAR(4) columns, the trailing spaces of values retrieved from the columns are removed from CHAR columns.
- 5 Set column default value for VARCHAR
- 6 VARCHAR column with default value
All CHAR and VARCHAR values in MySQL are compared without regard to any trailing spaces
<source lang="sql">
mysql> mysql> CREATE TABLE myTable (myname CHAR(10), yourname VARCHAR(10)); Query OK, 0 rows affected (0.03 sec) mysql> mysql> INSERT INTO myTable VALUES ("Monty ", "Monty "); Query OK, 1 row affected (0.01 sec) mysql> mysql> SELECT myname = "Monty ", yourname = "Monty " FROM myTable; +--------------------+----------------------+ | myname = "Monty " | yourname = "Monty " | +--------------------+----------------------+ | 1 | 1 | +--------------------+----------------------+ 1 row in set (0.00 sec) mysql> mysql> drop table myTable; Query OK, 0 rows affected (0.02 sec) mysql> mysql></source>
CHAR and VARCHAR
VARCHAR is a variable length and the CHAR is not.
CHAR types are used for fixed lengths.
If you declare a CHAR(10), all values stored in this column will be 10 bytes long, even if it is only 3 characters long.
MySQL pads this value to fit the size that was declared.
10. 5. Char Varchar 10. 5. 1. CHAR and VARCHAR 10. 5. 2. <A href="/Tutorial/MySQL/0200__Data-Types/DifferencesbetweenCHARandVARCHAR.htm">Differences between CHAR and VARCHAR</a> 10. 5. 3. <A href="/Tutorial/MySQL/0200__Data-Types/SetcolumndefaultvalueforVARCHAR.htm">Set column default value for VARCHAR</a> 10. 5. 4. <A href="/Tutorial/MySQL/0200__Data-Types/VARCHARcolumnwithdefaultvalue.htm">VARCHAR column with default value</a> 10. 5. 5. <A href="/Tutorial/MySQL/0200__Data-Types/AllCHARandVARCHARvaluesinMySQLarecomparedwithoutregardtoanytrailingspaces.htm">All CHAR and VARCHAR values in MySQL are compared without regard to any trailing spaces</a> 10. 5. 6. <A href="/Tutorial/MySQL/0200__Data-Types/IfagivenvalueisstoredintotheCHAR4andVARCHAR4columnsthetrailingspacesofvaluesretrievedfromthecolumnsareremovedfromCHARcolumns.htm">If a given value is stored into the CHAR(4) and VARCHAR(4) columns, the trailing spaces of values retrieved from the columns are removed from CHAR columns.</a>
Differences between CHAR and VARCHAR
Value CHAR(4) Storage Required VARCHAR(4) Storage Required "" " " 4 bytes "" 1 byte "ab" "ab " 4 bytes "ab" 3 bytes "abcd" "abcd" 4 bytes "abcd" 5 bytes "abcdefgh" "abcd" 4 bytes "abcd" 5 bytes
If a given value is stored into the CHAR(4) and VARCHAR(4) columns, the trailing spaces of values retrieved from the columns are removed from CHAR columns.
<source lang="sql">
mysql> mysql> CREATE TABLE myTable (v VARCHAR(4), c CHAR(4)); Query OK, 0 rows affected (0.01 sec) mysql> mysql> INSERT INTO myTable VALUES ("ab ", "ab "); Query OK, 1 row affected (0.00 sec) mysql> mysql> SELECT CONCAT("(", v, ")"), CONCAT("(", c, ")") FROM myTable; +---------------------+---------------------+ | CONCAT("(", v, ")") | CONCAT("(", c, ")") | +---------------------+---------------------+ | (ab ) | (ab) | +---------------------+---------------------+ 1 row in set (0.00 sec) mysql> mysql> drop table myTable; Query OK, 0 rows affected (0.00 sec) mysql></source>
Set column default value for VARCHAR
<source lang="sql">
mysql> mysql> CREATE TABLE myTable
-> ( -> ID SMALLINT UNSIGNED NOT NULL, -> Year YEAR NOT NULL, -> City VARCHAR(40) NOT NULL DEFAULT "Unknown" -> ) -> ENGINE=INNODB;
Query OK, 0 rows affected (0.08 sec) mysql> mysql> desc myTable; +-------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------------------+------+-----+---------+-------+ | ID | smallint(5) unsigned | NO | | | | | Year | year(4) | NO | | | | | City | varchar(40) | NO | | Unknown | | +-------+----------------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> mysql> drop table myTable; Query OK, 0 rows affected (0.03 sec)</source>
VARCHAR column with default value
<source lang="sql">
mysql> mysql> mysql> CREATE TABLE myTable
-> ( -> ID SMALLINT UNSIGNED NOT NULL, -> City VARCHAR(40) NOT NULL DEFAULT "Unknown" -> );
Query OK, 0 rows affected (0.05 sec) mysql> mysql> desc myTable; +-------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------------------+------+-----+---------+-------+ | ID | smallint(5) unsigned | NO | | | | | City | varchar(40) | NO | | Unknown | | +-------+----------------------+------+-----+---------+-------+ 2 rows in set (0.01 sec) mysql> mysql> drop table myTable; Query OK, 0 rows affected (0.00 sec) mysql></source>