MySQL Tutorial/Data Types/String

A double quotation inside a string quoted with single quotation needs no special treatment.

mysql>
mysql> CREATE TABLE employee (
    ->      name CHAR(30) NOT NULL
    -> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> INSERT INTO employee (name) VALUES (""A");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> SELECT * FROM employee;
+------+
| name |
+------+
| "A   |
+------+
1 row in set (0.00 sec)
mysql>
mysql> drop table employee;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>

A " inside a string quoted with " may be written as "".

mysql>
mysql> CREATE TABLE employee (
    ->      name CHAR(30) NOT NULL
    -> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> INSERT INTO employee (name) VALUES ("""A");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> SELECT * FROM employee;
+------+
| name |
+------+
| "A   |
+------+
1 row in set (0.00 sec)
mysql>
mysql> drop table employee;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>

A single quotation inside a string quoted with double quotation needs no special treatment.

mysql>
mysql>
mysql> CREATE TABLE employee (
    ->      name CHAR(30) NOT NULL
    -> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> INSERT INTO employee (name) VALUES (""A");
Query OK, 1 row affected (0.02 sec)
mysql>
mysql> SELECT * FROM employee;
+------+
| name |
+------+
| "A   |
+------+
1 row in set (0.00 sec)
mysql>
mysql> drop table employee;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>

Character or String Data Types

The storage that is needed for each type is determined by the length of the string.

String Types

Type Name Max Size CHAR(X) 255 bytes VARCHAR(X) 255 bytes TINYTEXT 255 bytes TINYBLOB 255 bytes TEXT 65535 bytes BLOB 65535 bytes MEDIUMTEXT 1.6MB MEDIUMBLOB 1.6MB LONGTEXT 4.2GB LONGBLOB 4.2GB

Hexadecimal Values

mysql>
mysql> SELECT x"4D7953514C";
+---------------+
| x"4D7953514C" |
+---------------+
| MySQL         |
+---------------+
1 row in set (0.00 sec)
mysql>

MySQL recognizes the following escape sequences.

\0 An ASCII 0 (NUL) character. \" A single quote (") character. \" A double quote (") character. \b A backspace character. \n A newline (linefeed) character. \r A carriage return character. \t A tab character. \Z ASCII 26 (Control-Z). See note following the table. \\ A backslash (\) character. \% A "%" character. See note following the table. \_ A "_" character. See note following the table.

mysql>
mysql>
mysql> SELECT "This\nIs\nFour\nLines";
+--------------------+
| This
Is
Four
Lines |
+--------------------+
| This
Is
Four
Lines |
+--------------------+
1 row in set (0.00 sec)
mysql>
mysql>

Precede the quote character by an escape character (\).

mysql>
mysql> CREATE TABLE employee (
    ->      name CHAR(30) NOT NULL
    -> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> INSERT INTO employee (name) VALUES ("\"A");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> SELECT * FROM employee;
+------+
| name |
+------+
| "A   |
+------+
1 row in set (0.00 sec)
mysql>
mysql> drop table employee;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>

String comparisons normally are case-insensitive

mysql>
mysql>
mysql>
mysql> CREATE TABLE Employee(
    ->     id            int,
    ->     first_name    VARCHAR(15),
    ->     last_name     VARCHAR(15),
    ->     start_date    DATE,
    ->     end_date      DATE,
    ->     salary        FLOAT(8,2),
    ->     city          VARCHAR(10),
    ->     description   VARCHAR(15)
    -> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->              values (1,"Jason",    "Martin",  "19960725",  "20060725", 1234.56, "Toronto",  "Programmer");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(2,"Alison",   "Mathews",  "19760321", "19860221", 6661.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(3,"James",    "Smith",    "19781212", "19900315", 6544.78, "Vancouver","Tester");
Query OK, 1 row affected (0.02 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(4,"Celia",    "Rice",     "19821024", "19990421", 2344.78, "Vancouver","Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(5,"Robert",   "Black",    "19840115", "19980808", 2334.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(6,"Linda",    "Green",    "19870730", "19960104", 4322.78,"New York",  "Tester");
Query OK, 1 row affected (0.02 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(7,"David",    "Larry",    "19901231", "19980212", 7897.78,"New York",  "Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(8,"BOWSER",    "Cat",     "19960917",  "20020415", 1232.78,"Vancouver", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id   | first_name | last_name | start_date | end_date   | salary  | city      | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
|    1 | Jason      | Martin    | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto   | Programmer  |
|    2 | Alison     | Mathews   | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester      |
|    3 | James      | Smith     | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester      |
|    4 | Celia      | Rice      | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager     |
|    5 | Robert     | Black     | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester      |
|    6 | Linda      | Green     | 1987-07-30 | 1996-01-04 | 4322.78 | New York  | Tester      |
|    7 | David      | Larry     | 1990-12-31 | 1998-02-12 | 7897.78 | New York  | Manager     |
|    8 | BOWSER     | Cat       | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester      |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.00 sec)
mysql>
mysql> SELECT * FROM employee WHERE first_name = "bowser";
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id   | first_name | last_name | start_date | end_date   | salary  | city      | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
|    8 | BOWSER     | Cat       | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester      |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT * FROM employee WHERE first_name = "BOWSER";
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id   | first_name | last_name | start_date | end_date   | salary  | city      | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
|    8 | BOWSER     | Cat       | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester      |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
1 row in set (0.00 sec)
mysql>
mysql> drop table Employee;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>

Strings

A string is a sequence of bytes or characters.

A String is enclosed within either single quote (") or double quote (") characters.

For example,

"a string"
"another string"

To include quote characters within a string

A " inside a string quoted with " may be written as "".

mysql>
mysql> CREATE TABLE employee (
    ->      name CHAR(30) NOT NULL
    -> );
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> INSERT INTO employee (name) VALUES ("""A");
Query OK, 1 row affected (0.03 sec)
mysql>
mysql> SELECT * FROM employee;
+------+
| name |
+------+
| "A   |
+------+
1 row in set (0.00 sec)
mysql>
mysql> drop table employee;
Query OK, 0 rows affected (0.00 sec)
mysql>

When inserting binary data into a string column (such as a BLOB column), the following characters must be represented by escape sequences:

NUL Represent this character by \0. \ Represent this character by \\. " Represent this character by \". " Represent this character by \".