程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> mysql支持的數據類型及其測試

mysql支持的數據類型及其測試

編輯：MySQL綜合教程

mysql支持的數據類型及其測試

1.基礎知識

1.1如何來查看mysql的幫助手冊

？int

Help float;

1.2創建表的規則

CREATE TABLE [IF NOT EXISTS] tbl_name(

字段名字段類型 [完整性的約束條件]);

1.3如何向表中插入數據

INSERT [INTO] tab_name [(字段名稱……)] VALUES (值……)

1.4Mysql數據類型

1.數值型

整數類型

TINYINT:范圍0~255;-128~127占用1字節

SMALLINT；范圍0~65535-32768~32767；占用2字節

MEDIUMINT；3字節

INT：4字節

BIGINT；8字節

浮點類型

FLOAT(D,,M):占用4字節；兩個參數M總共包括幾位,D小數點後幾位

DOUBLE(M,D):8字節；

定點類型

DECIMAL(D,M):內部以字符串形式存儲，占用大小為M+2個字節；

2.字符串類型

CHAR(M)定長 0-255 占M個字節 M代表字符串長度

VARCHAR（M）變長，范圍 0-65535，占M+1個字節

TINYTEXT、MEDIUMTEXT、 TEXT、LONGTEXT ：只能保存字符數據，內容長度+1個字節

ENUM：最多65535個值，保存值所對應的編號，最多可以存所列舉值中的一個

SET：可以選擇多個值，最多64個值，保存排列的編號，順序不區分

3.日期與時間類型

TIME 小時分鐘秒 3個字節

DATE 年月日 3個字節

DATETIME日期時間 8個字節

TIMESTAMP時間戳 4個字節

YEAR 年份 1個字節

2.實驗操作

2.1數值型

（1）整型

查看當前服務器下已有的數據庫：

mysql> SHOW DATABASES;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| test |

+--------------------+

4 rows in set (0.00 sec)

創建數據庫ran並查看是否創建成功：

mysql> CREATE DATABASE ran

-> ;

Query OK, 1 row affected (0.08 sec)

mysql> SHOW DATABASES;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| ran |

| test |

+--------------------+

5 rows in set (0.00 sec)

如何使用mysql的幫助手冊：\s或者help

查看INT類型的使用范圍等，

mysql> ? int

Name: 'INT'

Description:

INT[(M)] [UNSIGNED] [ZEROFILL]

A normal-size integer. The signed range is-2147483648 to 2147483647.

The unsigned range is 0 to 4294967295.

URL: http://dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html

查看FLOAT類型的使用范圍等，

mysql> help float

Name: 'FLOAT'

Description:

FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

A small (single-precision) floating-pointnumber. Permissible values

are -3.402823466E+38 to -1.175494351E-38,0, and 1.175494351E-38 to

3.402823466E+38. These are the theoreticallimits, based on the IEEE

standard. The actual range might beslightly smaller depending on your

hardware or operating system.

M is the total number of digits and D isthe number of digits following

the decimal point. If M and D are omitted,values are stored to the

limits permitted by the hardware. Asingle-precision floating-point

number is accurate to approximately 7decimal places.

UNSIGNED, if specified, disallows negativevalues.

Using FLOAT might give you some unexpectedproblems because all

calculations in MySQL are done with doubleprecision. See

http://dev.mysql.com/doc/refman/5.5/en/no-matching-rows.html.

URL: http://dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html

創建數據表之前應當使用數據庫，登陸mysql後默認沒有進入任何數據庫，使用數據庫ran並查看當前數據庫ran所擁有的表，如下所示ran還沒有創建任何表：

mysql> USE ran

Database changed

mysql> SHOW TABLES;

Empty set (0.00 sec)

為測試mysql中的整型創建數據表test1並查看是否創建成功：

mysql> CREATE TABLE IF NOT EXISTS test1(

-> num1 TINYINT,

-> num2 SMALLINT,

-> num3 MEDIUMINT,

-> num4 INT,

-> num5 BIGINT

-> );

Query OK, 0 rows affected (0.06 sec)

mysql> SHOW TABLES;

+---------------+

| Tables_in_ran |

+---------------+

| test1 |

+---------------+

1 row in set (0.00 sec)

向數據表中插入數據，測試mysql的整型：

mysql> INSERT INTO test1VALUES(127,65535,8388607,2147483647,231456789);

ERROR 1264 (22003): Out of range value forcolumn 'num2' at row 1

SMALLINT最大值為32767，超出范圍報錯，改成32767後插入成功：

mysql> INSERT INTO test1VALUES(127,32767,8388607,2147483647,231456789);

Query OK, 1 row affected (0.03 sec)

查詢表中所有記錄

SELECT * FROM tab_name;

mysql> SELECT * FROM test1;

+------+-------+---------+------------+-----------+

| num1 | num2 | num3 | num4 | num5 |

+------+-------+---------+------------+-----------+

| 127 | 32767 | 8388607 | 2147483647 | 231456789 |

+------+-------+---------+------------+-----------+

1 row in set (0.00 sec)

字符串也可以變成整型，mysql支持數據類型的轉換

mysql> INSERT INTO test1VALUES('1','2','3',45,09);

Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM test1;

+------+-------+---------+------------+-----------+

| num1 | num2 | num3 | num4 | num5 |

+------+-------+---------+------------+-----------+

| 127 | 32767 | 8388607 | 2147483647 | 231456789 |

| 1 | 2 | 3| 45 | 9 |

+------+-------+---------+------------+-----------+

2 rows in set (0.00 sec)

mysql> SELECT 1+'1';

+-------+

| 1+'1' |

+-------+

| 2 |

+-------+

1 row in set (0.00 sec)

mysql> SELECT 1+'KAIKEBA';

+-------------+

| 1+'KAIKEBA' |

+-------------+

| 1 |

+-------------+

1 row in set, 1 warning (0.00 sec)

mysql> SELECT 1+'3kaikeba';

+--------------+

| 1+'3kaikeba' |

+--------------+

| 4 |

+--------------+

1 row in set, 1 warning (0.00 sec)

（2）浮點數類型與定點類型

為了測試mysql的浮點數和定點數建立數據表test2:

mysql> CREATE TABLE IF NOT EXISTS test2(

-> Num1 FLOAT(6,2),

-> Num2 DOUBLE(6,2),

-> NUM3 DECIMAL(6,2)

-> );

Query OK, 0 rows affected (0.05 sec)

mysql> SHOW TABLES;

+---------------+

| Tables_in_ran |

+---------------+

| test1 |

| test2 |

+---------------+

2 rows in set (0.00 sec)

向數據表test2中插入范圍內的數據

mysql> INSERT INTO test2VALUES(9999.99,9999.99,9999.99);

Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM test2;

+---------+---------+---------+

| Num1 | Num2 | NUM3 |

+---------+---------+---------+

| 9999.99 | 9999.99 | 9999.99 |

+---------+---------+---------+

1 row in set (0.00 sec)

當插入的數據整數超出了范圍時，報錯：

mysql> INSERT INTO test2VALUES(19999.99,9999.99,9999.99);

ERROR 1264 (22003): Out of range value forcolumn 'Num1' at row 1

小數位超過范圍時，插入成功，但是報警告信息

ysql> INSERT INTO test2VALUES(23.34567,12.2345,67.8902);

Query OK, 1 row affected, 1 warning (0.02sec)

mysql> SELECT * FROM test2;

+---------+---------+---------+

| Num1 | Num2 | NUM3 |

+---------+---------+---------+

| 9999.99 | 9999.99 | 9999.99 |

| 23.35 | 12.23 | 67.89 |

+---------+---------+---------+

2 rows in set (0.00 sec)

數值是否會四捨五入呢？都進行四捨五入了。定點數超出范圍是會產生一條警告，而浮點型不會有警告。

mysql> INSERT INTO test2VALUES(12.5678,23.5678,34.5678);

Query OK, 1 row affected, 1 warning (0.05sec)

mysql> SELECT * FROM test2;

+---------+---------+---------+

| Num1 | Num2 | NUM3 |

+---------+---------+---------+

| 9999.99 | 9999.99 | 9999.99 |

| 23.35 | 12.23 | 67.89 |

| 12.57 | 23.57 | 34.57 |

+---------+---------+---------+

3 rows in set (0.00 sec)

2.2日期時間類型

（1）Time類型3個字節

mysql> CREATE TABLE IF NOT EXISTStesttime(

-> a Time

-> );

Query OK, 0 rows affected (0.08 sec)

插入時間的第一種形式：

mysql> INSERT testtime VALUES('13:14:24');

Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM testtime;

+----------+

| a |

+----------+

| 13:14:24 |

+----------+

1 row in set (0.03 sec)

插入數字0也可以：

mysql> INSERT testtime VALUES(0);

Query OK, 1 row affected (0.06 sec)

mysql> SELECT * FROM testtime;

+----------+

| a |

+----------+

| 13:14:24 |

| 00:00:00 |

+----------+

2 rows in set (0.00 sec)

插入時間是不用分隔符：

mysql> INSERT INTO testtimeVALUES('101010');

Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM testtime;

+----------+

| a |

+----------+

| 13:14:24 |

| 00:00:00 |

| 10:10:10 |

+----------+

3 rows in set (0.00 sec)

插入時間是在前面加上天：

mysql> INSERT INTO testtime VALUES('310:10:10');

Query OK, 1 row affected (0.16 sec)

mysql> SELECT * FROM testtime;

+----------+

| a |

+----------+

| 13:14:24 |

| 00:00:00 |

| 10:10:10 |

| 82:10:10 |

+----------+

4 rows in set (0.00 sec)

mysql> INSERT testtime VALUES('0');

Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM testtime;

+----------+

| a |

+----------+

| 13:14:24 |

| 00:00:00 |

| 10:10:10 |

| 82:10:10 |

| 15:51:51 |

| 00:00:00 |

+----------+

6 rows in set (0.00 sec)

mysql> INSERT testtime VALUES(123546);

Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM testtime;

+----------+

| a |

+----------+

| 13:14:24 |

| 00:00:00 |

| 10:10:10 |

| 82:10:10 |

| 15:51:51 |

| 00:00:00 |

| 15:53:16 |

| 12:35:46 |

+----------+

8 rows in set (0.00 sec)

時間類型的插入形式總結：

INSERT testtime VALUES(‘13:14:23’);13:14:23

INSERT testtime VALUES(0);00:00:00

INSERT testtime VALUES(‘101010’);10:10:10

INSERT testtime VALUES(‘3 10：10：10’);82:10:10

INSERT testtime VALUES(‘5 10-10-20’);//不成功的

INSERT testtime VALUES(‘0’);00:00:00

INSERT testtime VALUES(124536);12:45:36

得到當前系統時間

NOW();

CURRENT_TIME

CURRENT_TIME()

mysql> INSERT testtime VALUES(NOW());

Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM testtime;

+----------+

| a |

+----------+

| 13:14:24 |

| 00:00:00 |

| 10:10:10 |

| 82:10:10 |

| 15:51:51 |

+----------+

5 rows in set (0.00 sec)

mysql> INSERT testtime VALUES(CURRENT_TIME);

Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM testtime;

+----------+

| a |

+----------+

| 13:14:24 |

| 00:00:00 |

| 10:10:10 |

| 82:10:10 |

| 15:51:51 |

| 00:00:00 |

| 15:53:16 |

+----------+

7 rows in set (0.00 sec)

//時間類型的最小值；

mysql> INSERT testtimeVALUES('-838:59:59');

Query OK, 1 row affected (0.01 sec)

//時間類型的最大值：

mysql> INSERT testtimeVALUES('838:59:59');

Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM testtime;

+------------+

| a |

+------------+

| 13:14:24 |

| 00:00:00 |

| 10:10:10 |

| 82:10:10 |

| 15:51:51 |

| 00:00:00 |

| 15:53:16 |

| 12:35:46 |

| -838:59:59 |

| 838:59:59 |

+------------+

10 rows in set (0.00 sec)

//超出時間類型范圍報錯：

mysql> INSERT testtimeVALUES('838:69:59');

ERROR 1292 (22007): Incorrect time value:'838:69:59' for column 'a' at row 1

（2）DATE日期類型3個字節

1000-01-01~9999-12-31

CREATE TABLE IF NOT EXISTS testdate(

A DATE);

INSERT testdate VALUES(‘1000-01-01’);

INSERT testdate VALUES(‘2014/12/16’);

INSERT testdate VALUES(‘20120523’);

INSERT testdate VALUES(‘2014@12@16’);

得到當前系統的日期

NOW();

CURRENT_DATE

CURRENT_DATE();

INSERT testdate VALUES(NOW());

INSERT testdate VALUES(CURRENT_DATE);

INSERT testdate VALUES(CURRENT_DATE());

INSERT testdate VALUES(0);

實驗結果：

mysql> ? DATE

Name: 'DATE'

Description:

DATE

A date. The supported range is '1000-01-01'to '9999-12-31'. MySQL

displays DATE values in 'YYYY-MM-DD'format, but permits assignment of

values to DATE columns using either stringsor numbers.

URL:http://dev.mysql.com/doc/refman/5.5/en/date-and-time-type-overview.html

mysql> CREATE TABLE IF NOT EXISTStestdate(

-> a DATE);

Query OK, 0 rows affected (0.06 sec)

mysql> INSERT testdateVALUES('1000-01-01');

Query OK, 1 row affected (0.03 sec)

mysql> INSERT testdateVALUES('2014/12/13');

Query OK, 1 row affected (0.03 sec)

mysql> INSERT testdate VALUES(20151223);

Query OK, 1 row affected (0.06 sec)

mysql> INSERT testdateVALUES('2012@03@12');

Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM testdate;

+------------+

| a |

+------------+

| 1000-01-01 |

| 2014-12-13 |

| 2015-12-23 |

| 2012-03-12 |

+------------+

4 rows in set (0.00 sec)

mysql> INSERT testdateVALUES(CURRENT_DATE);

Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM testdate;

+------------+

| a |

+------------+

| 1000-01-01 |

| 2014-12-13 |

| 2015-12-23 |

| 2012-03-12 |

| 2014-10-14 |

+------------+

5 rows in set (0.00 sec)

mysql> INSERT testdate VALUES(0);

Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM testdate;

+------------+

| a |

+------------+

| 1000-01-01 |

| 2014-12-13 |

| 2015-12-23 |

| 2012-03-12 |

| 2014-10-14 |

| 0000-00-00 |

+------------+

6 rows in set (0.00 sec)

mysql> INSERT testdate VALUES(NOW());

Query OK, 1 row affected, 1 warning (0.03sec)

mysql> SELECT * FROM testdate;

+------------+

| a |

+------------+

| 1000-01-01 |

| 2014-12-13 |

| 2015-12-23 |

| 2012-03-12 |

| 2014-10-14 |

| 0000-00-00 |

| 2014-10-14 |

+------------+

7 rows in set (0.00 sec)

（3）DATETIME類型8個字節

CREATE TABLE IF NOT EXISTS testdatetime(

S DATETIME);

INSERT testdatetime VALUES(‘2014-8-3114:14:58’);

INSERT testdatetime VALUES(NOW());

需要指定分隔符

實驗結果：

mysql> CREATE TABLE IF NOT EXISTStestdatetime(

-> s DATETIME);

Query OK, 0 rows affected (0.13 sec)

mysql> INSERT testdatetimeVALUES('2014-8-31 13:23:56');

Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM testdatetime;

+---------------------+

| s |

+---------------------+

| 2014-08-31 13:23:56 |

+---------------------+

1 row in set (0.00 sec)

mysql> INSERT testdatetimeVALUES(NOW());

Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM testdatetime;

+---------------------+

| s |

+---------------------+

| 2014-08-31 13:23:56 |

| 2014-10-14 16:09:01 |

+---------------------+

2 rows in set (0.00 sec)

mysql> INSERT testdatetimeVALUES('2014@09@01 14-25-36');

Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM testdatetime;

+---------------------+

| s |

+---------------------+

| 2014-08-31 13:23:56 |

| 2014-10-14 16:09:01 |

| 2014-09-01 14:25:36 |

+---------------------+

3 rows in set (0.00 sec)

（4）TIMESTAMP類型4個字節

1970-01-018：01：01到2038-01-19 11:14:07

與DATETIME顯示格式一樣

CREATE TABLE IF NOT EXISTS testtimestamp(

S TIMESTAMP);

實驗結果：

mysql> CREATE TABLE IF NOT EXISTStesttimestamp(

-> s TIMESTAMP);

Query OK, 0 rows affected (0.06 sec)

mysql> INSERT testtimestampVALUES(NOW());

Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM testtimestamp;

+---------------------+

| s |

+---------------------+

| 2014-10-14 16:14:48 |

+---------------------+

1 row in set (0.00 sec)

（5）YEAR類型

1971-2055年

CREATE TABLE IF NOT EXISTS testyear(

A YEAR);

\C撤銷當前命令執行

兩位轉換規則：

INSERT testyear VALUES(12);2012(00-69)加上2000

INSERT testyear VALUES(78);1978（70-99）加上1900

INSERT testyear VALUES(0);0000

INSERT testyear VALUES(‘0’);2000

INSERT testyear VALUES(‘00’);2000

INSERT testyear VALUES(2014);2014

INSERT testyear VALUES(‘2015’);2015

在實際開發中有可能用整形保存時間戳來替代和日期時間相關的一些這樣的數據。

mysql> CREATE TABLE IF NOT EXISTStestyear(

-> a YEAR);

Query OK, 0 rows affected (0.06 sec)

mysql> INSERT testyear VALUES('2014');

Query OK, 1 row affected (0.03 sec)

mysql> INSERT testyear VALUES(2015);

Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM testyear;

+------+

| a |

+------+

| 2014 |

| 2015 |

+------+

2 rows in set (0.00 sec)

mysql> INSERT testyear VALUES(0);

Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM testyear;

+------+

| a |

+------+

| 2014 |

| 2015 |

| 0000 |

+------+

3 rows in set (0.00 sec)

mysql> INSERT testyear VALUES('0');

Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM testyear;

+------+

| a |

+------+

| 2014 |

| 2015 |

| 0000 |

| 2000 |

+------+

4 rows in set (0.00 sec)

mysql> INSERT testyear VALUES('00');

Query OK, 1 row affected (0.09 sec)

mysql> SELECT * FROM testyear;

+------+

| a |

+------+

| 2014 |

| 2015 |

| 0000 |

| 2000 |

+------+

5 rows in set (0.00 sec)

mysql> INSERT testyear VALUES(12);

Query OK, 1 row affected (0.08 sec)

mysql> SELECT * FROM testyear;

+------+

| a |

+------+

| 2014 |

| 2015 |

| 0000 |

| 2000 |

| 2012 |

+------+

6 rows in set (0.00 sec)

mysql> INSERT testyear VALUES(70);

Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM testyear;

+------+

| a |

+------+

| 2014 |

| 2015 |

| 0000 |

| 2000 |

| 2012 |

| 1970 |

+------+

7 rows in set (0.00 sec)

mysql> INSERT testyear VALUES('000');

Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM testyear;

+------+

| a |

+------+

| 2014 |

| 2015 |

| 0000 |

| 2000 |

| 2012 |

| 1970 |

| 2000 |

+------+

8 rows in set (0.00 sec)

2.3字符類型

（1）CHAR(M):定長，0-255個字節

VARCHAR(M):變長，占M+1個字節

創建數據表同時插入A字符；

mysql> CREATE TABLE IF NOT EXISTSteststring(

-> str1 CHAR(5),

-> str2 VARCHAR(5)

-> );

Query OK, 0 rows affected (0.05 sec)

mysql> INSERT teststringVALUES('A','A');

Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM teststring;

+------+------+

| str1 | str2 |

+------+------+

| A | A |

+------+------+

1 row in set (0.00 sec)

當超過5個字符時報錯：

mysql> INSERT teststringVALUES('Abcdef','bcdefA');

ERROR 1406 (22001): Data too long forcolumn 'str1' at row 1

在插入中文之前需要臨時轉換下客戶端的編碼方式，否則會出現亂碼或意想不到的錯誤：

SET NAMES GBK;//只針對當前連接有效，或者在他之後有效；如果退出後需要重新設置編碼方式。

一個中文代表一個字符

INSERT teststring VALUES(‘開課吧開課’，‘大家快學習’）;

怎麼樣在命令行提示符中輸入中文：按ctrl+空格切換輸入法就可以辦到

在當前連接下或不退出情況下，可以輸入中文了。

否則插入出錯。

查看編碼方式：

Status

實驗結果：

設置mysql數據庫之前查看mysql的編碼方式：都是UTF8

mysql> \s

--------------

Connection id: 2

Current database: ran

Current user: root@localhost

SSL: Notin use

Using delimiter: ;

Server version: 5.5.19 MySQL Community Server (GPL)

Protocol version: 10

Connection: localhostvia TCP/IP

Server characterset: utf8

Db characterset: utf8

Client characterset: utf8

Conn. characterset: utf8

TCP port: 3306

Uptime: 2hours 5 min 13 sec

Threads: 1 Questions: 117 Slow queries:0 Opens: 57 Flush tables: 1 Open tables: 1 Queries per second avg: 0.015

--------------

mysql> status;

--------------

Connection id: 2

Current database: ran

Current user: root@localhost

SSL: Notin use

Using delimiter: ;

Server version: 5.5.19 MySQL Community Server (GPL)

Protocol version: 10

Connection: localhostvia TCP/IP

Server characterset: utf8

Db characterset: utf8

Client characterset: utf8

Conn. characterset: utf8

TCP port: 3306

Uptime: 2hours 5 min 31 sec

Threads: 1 Questions: 119 Slow queries: 0 Opens: 57 Flush tables: 1 Open tables:1 Queries per second avg: 0.015

//設置mysql客戶端的編碼方式為GBK

mysql> SET NAMES GBK;

Query OK, 0 rows affected (0.00 sec)

//再次查看mysql的編碼方式已經改變：

mysql> \s

--------------

Connection id: 2

Current database: ran

Current user: root@localhost

SSL: Notin use

Using delimiter: ;

Server version: 5.5.19 MySQL Community Server (GPL)

Protocol version: 10

Connection: localhostvia TCP/IP

Server characterset: utf8

Db characterset: utf8

Client characterset: gbk

Conn. characterset: gbk

TCP port: 3306

Uptime: 2hours 6 min 46 sec

Threads: 1 Questions: 122 Slow queries:0 Opens: 57 Flush tables: 1 Open tables: 1 Queries per second avg: 0.016

//插入漢子，一個漢子代表一個字符：

mysql> INSERT teststring VALUES('開課吧開課','大家快學習');

Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM teststring;

+------------+------------+

| str1 | str2 |

+------------+------------+

| A | A |

| 開課吧開課 | 大家快學習 |

+------------+------------+

2 rows in set (0.00 sec)

mysql> INSERT teststring VALUES('開課吧開課','大家快學習.');

ERROR 1406 (22001): Data too long forcolumn 'str2' at row 1

CHAR與VARCHAR區別：

（1）存儲方式與檢索方式不同；保存數據是，當數據長度小於指定長度，CHAR類型會在後面用空格填充指定長度，當檢索數據時會去掉後面的空格；PHP可以用TRIM或RTRIM函數將查詢出來的數據中的空格去掉。VARCHAR保存時不填充空格，當檢索數據時如果有空格，空格保留。

（2）CHAR效率高於VARCHAR,但浪費了空間。長度超過255或者長度不固定時，只能用VARCHAR.

（2）TEXT:只能保存字符數據,存大量文本時可以用TEXT.

TINYTEXT

TEXT

MEDIUMTEXT

LONGTEXT

當長度超過6萬多字時，使用TEXT,但是能用VARCHAR的地方盡量不用TEXT類型。

數據檢索效率：CHAR>VARCHAR>TEXT

實驗結果：

mysql> CREATE TABLE testtext(

-> a TINYTEXT,

-> b TEXT,

-> c MEDIUMTEXT,

-> d LONGTEXT);

Query OK, 0 rows affected (0.11 sec)

（3）ENUM枚舉類型

ENUM(‘值1’,’值2’,……)：最多65535個值

ENUM:保存值所對應的編號，插入值時只能插入列舉值中的一個

CREATE TABLE testenum(

Sex ENUM(‘男’,’女’,’保密’));

INSERT testenum VALUES(‘男’);

INSERT testenum VALUES(1);

INSERT testenum VALUES(2);

INSERT testenum VALUES(3);

實驗結果：

mysql> CREATE TABLE testenum(

-> sex ENUM('男','女','保密'));

Query OK, 0 rows affected (0.06 sec)

mysql> INSERT testenum VALUES(1);

Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM testenum;

+------+

| sex |

+------+

| 男 |

+------+

1 row in set (0.00 sec)

mysql> INSERT testenum VALUES('保密');

Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM testenum;

+------+

| sex |

+------+

| 男 |

| 保密 |

+------+

2 rows in set (0.00 sec)

mysql> INSERT testenum VALUES(3);

Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM testenum;

+------+

| sex |

+------+

| 男 |

| 保密 |

+------+

3 rows in set (0.00 sec)

（4）SET:保存排列的編號，不區分順序

最多保存64個值，插值時可以插入多個值，必須插入集合中的值。

CREATE TABLE testset(

Fav SET(‘A’,’’B,’C’,’D’));

INSERT testset VALUES(‘A,B,C’);

INSERT testset VALUES(‘B,D’);

INSERT testset VALUES(‘C,D’);

INSERT testset VALUES(‘D,E’);(出錯）

做權限管理時，使用集合非常簡單。

實驗結果：

mysql> CREATE TABLE testset(

-> fav SET('A','B','C','D'));

Query OK, 0 rows affected (0.09 sec)

mysql> INSERT testset VALUES('A,B,C');

Query OK, 1 row affected (0.03 sec)

mysql> INSERT testset VALUES('B,D');

Query OK, 1 row affected (0.02 sec)

mysql> INSERT testset VALUES('B,C');

Query OK, 1 row affected (0.03 sec)

mysql> INSERT testset VALUES('B,E');

ERROR 1265 (01000): Data truncated forcolumn 'fav' at row 1

mysql> SELECT * FROM testset;

+-------+

| fav |

+-------+

| A,B,C |

| B,D |

| B,C |

+-------+

3 rows in set (0.00 sec)

MySQL綜合教程

重裝MySQL數據庫失敗的問題破解

以下的文章主要是介紹一種更為快捷的方法來實現重裝MySQL

mysql在linux下去分表名大小寫的問題

我做開發時候是用windows 的所以沒感覺&nb

MySQL常見錯誤，mysql常見面試題

MySQL常見錯誤，mysql常見面試題1. To

Windows下MySQL 5.6安裝及配置詳細圖解

一、安裝前的准備 1、下載安裝程序包，可到MySQL官方網

Mysql¸ß¼¶Ö®´æ´¢¹ý³Ì

Mysql¸ß¼¶Ö®´æ´¢¹ý³Ì ¸ÅÄîÀà&#

MySQL Workbench 5.2.37發布附下載

MySQL Workbench 5.2.37 發布了，下載

熱門圖文

mysql 語句大全 MySQL刪除更新數據時報1175錯誤的問題也議MySQL中隱式轉換怎麼樣遠程調用ACCESS數據庫 SQL Server系統數據庫的工作原理用Access將XLS與MDB文件格式互相轉換教你用bat腳本備份mysql方法 Oracle客戶端 NLS_LANG 的設置方法

欄目導航

MYSQL入門知識 SQL基礎知識關於MYSQL數據庫 Mariadb數據庫 MySQL綜合教程