原文: mysql支持的數(shù)據(jù)類型及其測試

1.基礎(chǔ)知識

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

？int

Help float;

1.2創(chuàng)建表的規(guī)則

CREATE TABLE [IF NOT EXISTS] tbl_name(

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

1.3如何向表中插入數(shù)據(jù)

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

1.4Mysql數(shù)據(jù)類型

1.數(shù)值型

整數(shù)類型

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

SMALLINT；范圍0~65535-32768~32767；占用2字節(jié)

MEDIUMINT；3字節(jié)

INT：4字節(jié)

BIGINT；8字節(jié)

浮點(diǎn)類型

FLOAT(D,,M):占用4字節(jié)；兩個參數(shù)M總共包括幾位,D小數(shù)點(diǎn)后幾位

DOUBLE(M,D):8字節(jié)；

定點(diǎn)類型

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

2.字符串類型

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

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

TINYTEXT、MEDIUMTEXT、 TEXT、LONGTEXT ：只能保存字符數(shù)據(jù)，內(nèi)容長度+1個字節(jié)

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

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

3.日期與時間類型

TIME 小時分鐘秒 3個字節(jié)

DATE 年月日 3個字節(jié)

DATETIME日期時間 8個字節(jié)

TIMESTAMP時間戳 4個字節(jié)

YEAR 年份 1個字節(jié)

2.實(shí)驗(yàn)操作

2.1數(shù)值型

（1）整型

查看當(dāng)前服務(wù)器下已有的數(shù)據(jù)庫：

mysql> SHOW DATABASES;

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

| Database?????? ????|

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

| information_schema |

| mysql????????????? |

| performance_schema |

| test?????????????? |

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

4 rows in set (0.00 sec)

創(chuàng)建數(shù)據(jù)庫ran并查看是否創(chuàng)建成功：

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或者h(yuǎn)elp

查看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

創(chuàng)建數(shù)據(jù)表之前應(yīng)當(dāng)使用數(shù)據(jù)庫，登陸mysql后默認(rèn)沒有進(jìn)入任何數(shù)據(jù)庫，使用數(shù)據(jù)庫ran并查看當(dāng)前數(shù)據(jù)庫ran所擁有的表，如下所示ran還沒有創(chuàng)建任何表：

mysql> USE ran

Database changed

mysql> SHOW TABLES;

Empty set (0.00 sec)

為測試mysql中的整型創(chuàng)建數(shù)據(jù)表test1并查看是否創(chuàng)建成功：

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)

向數(shù)據(jù)表中插入數(shù)據(jù)，測試mysql的整型：

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

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

SMALLINT最大值為32767，超出范圍報(bào)錯，改成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支持?jǐn)?shù)據(jù)類型的轉(zhuǎn)換

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）浮點(diǎn)數(shù)類型與定點(diǎn)類型

為了測試mysql的浮點(diǎn)數(shù)和定點(diǎn)數(shù)建立數(shù)據(jù)表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)

向數(shù)據(jù)表test2中插入范圍內(nèi)的數(shù)據(jù)

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)

當(dāng)插入的數(shù)據(jù)整數(shù)超出了范圍時，報(bào)錯：

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

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

小數(shù)位超過范圍時，插入成功，但是報(bào)警告信息

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)

數(shù)值是否會四舍五入呢？都進(jìn)行四舍五入了。定點(diǎn)數(shù)超出范圍是會產(chǎn)生一條警告，而浮點(diǎn)型不會有警告。

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個字節(jié)

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)

插入數(shù)字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)

時間類型的插入形式總結(jié)：

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

得到當(dāng)前系統(tǒng)時間

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)

//超出時間類型范圍報(bào)錯：

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

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

（2）DATE日期類型3個字節(jié)

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’);

得到當(dāng)前系統(tǒng)的日期

NOW();

CURRENT_DATE

CURRENT_DATE();

INSERT testdate VALUES(NOW());

INSERT testdate VALUES(CURRENT_DATE);

INSERT testdate VALUES(CURRENT_DATE());

INSERT testdate VALUES(0);

實(shí)驗(yàn)結(jié)果：

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個字節(jié)

CREATE TABLE IF NOT EXISTS testdatetime(

S DATETIME);

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

INSERT testdatetime VALUES(NOW());

需要指定分隔符

實(shí)驗(yàn)結(jié)果：

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個字節(jié)

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

與DATETIME顯示格式一樣

CREATE TABLE IF NOT EXISTS testtimestamp(

S TIMESTAMP);

實(shí)驗(yàn)結(jié)果：

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撤銷當(dāng)前命令執(zhí)行

兩位轉(zhuǎn)換規(guī)則：

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

在實(shí)際開發(fā)中有可能用整形保存時間戳來替代和日期時間相關(guān)的一些這樣的數(shù)據(jù)。

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個字節(jié)

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

創(chuàng)建數(shù)據(jù)表同時插入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)

當(dāng)超過5個字符時報(bào)錯：

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

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

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

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

一個中文代表一個字符

INSERT teststring VALUES(‘開課吧開課’，‘大家快學(xué)習(xí)’）;

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

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

否則插入出錯。

查看編碼方式：

Status

實(shí)驗(yàn)結(jié)果：

設(shè)置mysql數(shù)據(jù)庫之前查看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

//設(shè)置mysql客戶端的編碼方式為GBK

mysql> SET NAMES GBK;

Query OK, 0 rows affected (0.00 sec)

//再次查看mysql的編碼方式已經(jīng)改變：

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('開課吧開課','大家快學(xué)習(xí)');

Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM teststring;

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

| str1??????| str2?????? |

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

| A?????????| A????????? |

| 開課吧開課???????? | 大家快學(xué)習(xí)??????? |

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

2 rows in set (0.00 sec)

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

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

CHAR與VARCHAR區(qū)別：

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

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

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

TINYTEXT

TEXT

MEDIUMTEXT

LONGTEXT

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

數(shù)據(jù)檢索效率：CHAR>VARCHAR>TEXT

實(shí)驗(yàn)結(jié)果：

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:保存值所對應(yīng)的編號，插入值時只能插入列舉值中的一個

CREATE TABLE testenum(

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

INSERT testenum VALUES(‘男’);

INSERT testenum VALUES(1);

INSERT testenum VALUES(2);

INSERT testenum VALUES(3);

實(shí)驗(yàn)結(jié)果：

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:保存排列的編號，不區(qū)分順序

最多保存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’);(出錯）

做權(quán)限管理時，使用集合非常簡單。

實(shí)驗(yàn)結(jié)果：

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支持的數(shù)據(jù)類型及其測試

更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主

微信掃碼或搜索：z360901061

微信掃一掃加我為好友

QQ號聯(lián)系： 360901061

您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長非常感激您！手機(jī)微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。

【本文對您有幫助就好】元

2元

5元

10元

20元

自定義