MySQL Tutorial/Data Types/String
Содержание
- 1 A double quotation inside a string quoted with single quotation needs no special treatment.
- 2 A " inside a string quoted with " may be written as "".
- 3 A single quotation inside a string quoted with double quotation needs no special treatment.
- 4 Character or String Data Types
- 5 Hexadecimal Values
- 6 MySQL recognizes the following escape sequences.
- 7 Precede the quote character by an escape character (\).
- 8 String comparisons normally are case-insensitive
- 9 Strings
- 10 To include quote characters within a string
- 11 When inserting binary data into a string column (such as a BLOB column), the following characters must be represented by escape sequences:
A double quotation inside a string quoted with single quotation needs no special treatment.
<source lang="sql">
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></source>
A " inside a string quoted with " may be written as "".
<source lang="sql">
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></source>
A single quotation inside a string quoted with double quotation needs no special treatment.
<source lang="sql">
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></source>
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
<source lang="sql">
mysql> mysql> SELECT x"4D7953514C"; +---------------+ | x"4D7953514C" | +---------------+ | MySQL | +---------------+ 1 row in set (0.00 sec) mysql></source>
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.
<source lang="sql">
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></source>
Precede the quote character by an escape character (\).
<source lang="sql">
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></source>
String comparisons normally are case-insensitive
<source lang="sql">
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></source>
Strings
A string is a sequence of bytes or characters.
A String is enclosed within either single quote (") or double quote (") characters.
For example,
<source lang="sql">
"a string" "another string"</source>
To include quote characters within a string
A " inside a string quoted with " may be written as "".
<source lang="sql">
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></source>
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 \".
