Просмотр исходного текста страницы MySQL Tutorial/Data Types/Char Varchar

== All CHAR and VARCHAR values in MySQL are compared without regard to any trailing spaces==



   
  <!-- start source code -->
   
    <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==

<p>VARCHAR is a variable length and the CHAR is not.</p>
<p>CHAR types are used for fixed lengths.</p>
<p>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.</p>
<p>MySQL pads this value to fit the size that was declared.</p>
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.==



   
  <!-- start source code -->
   
    <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==



   
  <!-- start source code -->
   
    <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==



   
  <!-- start source code -->
   
    <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>