凡客建网站,互联网营销的概念,价钱网站建设,服务平台图片MySQL支持多种列类型#xff1a;数值类型、日期/时间类型和字符串(字符)类型。数值类型数值类型又分为整数型与小数型整数型下面的表显示了需要的每个整数类型的存储和范围创建一张表mysql CREATE TABLE t_int (int_1 TINYINT,int_2 SMALLINT,int_3 MEDIUMINT,int_4 INT,i…MySQL支持多种列类型数值类型、日期/时间类型和字符串(字符)类型。数值类型数值类型又分为整数型与小数型整数型下面的表显示了需要的每个整数类型的存储和范围创建一张表mysql CREATE TABLE t_int (int_1 TINYINT,int_2 SMALLINT,int_3 MEDIUMINT,int_4 INT,int_5 BIGINT);Query OK, 0 rows affectedmysql DESC t_int;------------------------------------------------| Field | Type | Null | Key | Default | Extra |------------------------------------------------| int_1 | tinyint(4) | YES | | NULL | || int_2 | smallint(6) | YES | | NULL | || int_3 | mediumint(9) | YES | | NULL | || int_4 | int(11) | YES | | NULL | || int_5 | bigint(20) | YES | | NULL | |------------------------------------------------5 rows in set插入数据只能插入整型数据类型不正确或者超出范围都会导致插入数据失败mysql INSERT INTO t_int VALUES(100, 100, 100, 100, 100);Query OK, 1 row affectedmysql INSERT INTO t_int VALUES(a, b, 100, 200, 300); -- 数据类型不正确1366 - Incorrect integer value: a for column int_1 at row 1mysql INSERT INTO t_int VALUES(255, 10000, 1000, 100000, 1000000); -- 超出范围1264 - Out of range value for column int_1 at row 1mysql SELECT * FROM t_int;-----------------------------------| int_1 | int_2 | int_3 | int_4 | int_5 |-----------------------------------| 100 | 100 | 100 | 100 | 100 |-----------------------------------1 row in setSQL中的数值类型全部都是默认有符号分正负如果要使用无符号数据则需要用UNSIGNED关键字对数据类型进行限定mysql ALTER TABLE t_int ADD COLUMN int_6 TINYINT UNSIGNED; -- 无符号类型Query OK, 1 row affectedRecords: 1 Duplicates: 0 Warnings: 0mysql DESC t_int;-------------------------------------------------------| Field | Type | Null | Key | Default | Extra |-------------------------------------------------------| int_1 | tinyint(4) | YES | | NULL | || int_2 | smallint(6) | YES | | NULL | || int_3 | mediumint(9) | YES | | NULL | || int_4 | int(11) | YES | | NULL | || int_5 | bigint(20) | YES | | NULL | || int_6 | tinyint(3) unsigned | YES | | NULL | |-------------------------------------------------------6 rows in setmysql INSERT INTO t_int VALUES(127, 10000, 100000, 100000, 1000000, 255); -- TINYINT无符号类型的取值范围为0-255Query OK, 1 row affectedmysql SELECT * FROM t_int;----------------------------------------------| int_1 | int_2 | int_3 | int_4 | int_5 | int_6 |----------------------------------------------| 100 | 100 | 100 | 100 | 100 | NULL || 127 | 10000 | 100000 | 100000 | 1000000 | 255 |----------------------------------------------2 rows in set给字段加了无符号的限定后数据插入成功了在查看表结构的时候发现每个字段的数据类型之后都会自带一个括号里面有指定的数字这个数字表示数据最终显示的位数例如-123表示显示4位(包括符号位)255表示显示3位实际上这个数字没有什么特别的含义只是默认告诉用户可以显示的宽度而已并不会改变数据的大小。mysql ALTER TABLE t_int ADD COLUMN int_7 TINYINT(1) UNSIGNED; -- 显示指定显示宽度Query OK, 2 rows affectedRecords: 2 Duplicates: 0 Warnings: 0mysql INSERT INTO t_int VALUES(127, 10000, 100000, 100000, 1000000, 255, 255);Query OK, 1 row affectedmysql SELECT * FROM t_int;-----------------------------------------------------| int_1 | int_2 | int_3 | int_4 | int_5 | int_6 | int_7 |-----------------------------------------------------| 100 | 100 | 100 | 100 | 100 | NULL | NULL || 127 | 10000 | 100000 | 100000 | 1000000 | 255 | NULL || 127 | 10000 | 100000 | 100000 | 1000000 | 255 | 255 |-----------------------------------------------------3 rows in set显示宽度的意义在于当数据不够显示宽度的时候可以使用ZEROFILL为数字列进行零填充这时MySQL自动为该列添加UNSIGNED属性mysql ALTER TABLE t_int ADD COLUMN int_8 TINYINT(2) UNSIGNED ZEROFILL;Query OK, 3 rows affectedRecords: 3 Duplicates: 0 Warnings: 0mysql INSERT INTO t_int VALUES(127, 10000, 100000, 100000, 1000000, 255, 255, 1);Query OK, 1 row affectedmysql SELECT * FROM t_int;------------------------------------------------------------| int_1 | int_2 | int_3 | int_4 | int_5 | int_6 | int_7 | int_8 |------------------------------------------------------------| 100 | 100 | 100 | 100 | 100 | NULL | NULL | NULL || 127 | 10000 | 100000 | 100000 | 1000000 | 255 | NULL | NULL || 127 | 10000 | 100000 | 100000 | 1000000 | 255 | 255 | NULL || 127 | 10000 | 100000 | 100000 | 1000000 | 255 | 255 | 01 |------------------------------------------------------------4 rows in setMySQL客户端如Navicat中可能不能正确显示零填充的效果可以使用命令行窗口进行查看。小数型SQL中将小数型又分为浮点型和定点型两种浮点型小数点浮动精度有限并且会丢失精度(会进行四舍五入)精度位数大概在7位定点型小数点固定精度固定不会丢失精度精度位数大概在15位浮点型对于浮点列类型在MySQL中单精度值使用4个字节双精度值使用8个字节。FLOAT类型用于表示近似数值数据类型。SQL标准允许在关键字FLOAT后面的括号内选择用位指定精度(但不能为指数范围)。MySQL还支持可选的只用于确定存储大小的精度规定0到23的精度对应FLOAT列的4字节单精度。24到53的精度对应DOUBLE列的8字节双精度。一个FLOAT类型的数字允许的值为-3.402823466E38至-1.175494351E-3801.175494351E-38至3.402823466E38这些是基于IEEE标准的理论值实际范围根据硬件或操作系统可能会稍微小一些。MySQL允许使用非标准语法FLOAT(M,D)或REAL(M,D)或DOUBLE PRECISION(M,D)。这里“(M,D)”表示该值一共显示M位整数其中D位位于小数点后面。例如定义为FLOAT(7,4)的一个列可以显示为-999.9999。MySQL保存值时进行四舍五入因此如果在FLOAT(7,4)列内插入999.00009近似结果是999.0001。下面以FLOAT为例子DOUBLE类似mysql CREATE TABLE t_float(float_1FLOAT,float_2 FLOAT(10, 2),float_3 FLOAT(6, 2));Query OK, 0 rows affectedmysql DESC t_float;-------------------------------------------------| Field | Type | Null | Key | Default | Extra |-------------------------------------------------| float_1 | float | YES | | NULL | || float_2 | float(10,2) | YES | | NULL | || float_3 | float(6,2) | YES | | NULL | |-------------------------------------------------3 rows in setmysql INSERT INTO t_float VALUES (1000.10,1000.10,1000.10); -- 正常数据Query OK, 1 row affectedmysql INSERT INTO t_float VALUES (1234567890,12345678.90,1234.56); -- 正常数据Query OK, 1 row affectedmysql INSERT INTO t_float VALUES (3e38,3.01e7,1234.56); -- 可以用科学计数法表示Query OK, 1 row affectedmysql INSERT INTO t_float VALUES (9999999999,99999999.99,9999.99); -- 插入最大值超过精度位数时系统会进行四舍五入Query OK, 1 row affectedmysql SELECT * FROM t_float;------------------------------------| float_1 | float_2 | float_3 |------------------------------------| 1000.1 | 1000.10 | 1000.10 || 1234570000 | 12345679.00 | 1234.56 || 3e38 | 30100000.00 | 1234.56 || 10000000000 | 100000000.00 | 9999.99 |------------------------------------4 rows in set在插入浮点型数据的时候整型部分是不能超出长度的但是小数部分可以超出长度(系统会自动四舍五入)mysql INSERT INTO t_float VALUES (123456,1234.123456768,123.9876543); -- 小数部分超出四舍五入Query OK, 1 row affectedmysql INSERT INTO t_float VALUES (123456,1234.12,12345.56);1264 - Out of range value for column float_3 at row 1mysql SELECT * FROM t_float;------------------------------------| float_1 | float_2 | float_3 |------------------------------------| 1000.1 | 1000.10 | 1000.10 || 1234570000 | 12345679.00 | 1234.56 || 3e38 | 30100000.00 | 1234.56 || 10000000000 | 100000000.00 | 9999.99 || 123456 | 1234.12 | 123.99 |------------------------------------5 rows in set (0.00 sec)超出精度范围的浮点数系统会自动进行四舍五入如果因为系统进位导致整数部分超出指定的长度这也是被允许的例如上面的第4条记录。为了保证最大可能的可移植性需要使用近似数值数据值存储的代码应使用FLOAT或DOUBLE PRECISION不规定精度或位数。定点型DECIMAL和NUMERIC类型在MySQL中视为相同的类型。它们用于保存必须为确切精度的值例如货币数据。当声明该类型的列时可以(并且通常要)指定精度和标度如salary DECIMAL(5,2)在该例子中5是精度2是标度。精度表示保存值的主要位数标度表示小数点后面可以保存的位数。在标准SQL中语法DECIMAL(M)等价于DECIMAL(M,0)。同样语法DECIMAL等价于DECIMAL(M,0)可以通过计算确定M的值。在MySQL中支持DECIMAL和NUMERIC数据类型的变量形式。M默认值是10。DECIMAL或NUMERIC的最大位数是65但具体的DECIMAL或NUMERIC列的实际范围受具体列的精度或标度约束。mysql CREATE TABLE t_decimal (float_1 FLOAT (10, 2),decimal_1 DECIMAL (10, 2));Query OK, 0 rows affectedmysql INSERT INTO t_decimal VALUES (12345678.90,12345678.90); -- 正常数据Query OK, 1 row affectedmysql INSERT INTO t_decimal VALUES (1234.123456,1234.1234356); -- 小数部分四舍五入Query OK, 1 row affectedmysql INSERT INTO t_decimal VALUES (99999999.99,99999999.99); -- 最大值Query OK, 1 row affectedmysql INSERT INTO t_decimal VALUES (99999999.99,99999999.999); -- 进位超出指定位数不允许1264 - Out of range value for column decimal_1 at row 1mysql SELECT * FROM t_decimal;---------------------------| float_1 | decimal_1 |---------------------------| 12345679.00 | 12345678.90 || 1234.12 | 1234.12 || 100000000.00 | 99999999.99 |---------------------------3 rows in set (0.00 sec)时间日期类型表示时间值的DATE和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。每个时间类型有一个有效值范围和一个“零”值当指定不合法的MySQL不能表示的值时使用“零”值。mysql CREATE TABLE t_date (id INT,date_1 DATETIME,date_2 TIMESTAMP,date_3 DATE,date_4 TIME,date_5 YEAR);Query OK, 0 rows affectedmysql DESC t_date; -- timestamap不允许为空有个默认值为当前时间------------------------------------------------------------------------------| Field | Type | Null | Key | Default | Extra |------------------------------------------------------------------------------| id | int(11) | YES | | NULL | || date_1 | datetime | YES | | NULL | || date_2 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP || date_3 | date | YES | | NULL | || date_4 | time | YES | | NULL | || date_5 | year(4) | YES | | NULL | |------------------------------------------------------------------------------6 rows in setmysql INSERT INTO t_date VALUES(1, 2018-6-24 15:17:58, 2018-6-24 15:18:14, 2018-6-24, 15:18:27, 2018); -- 正常数据Query OK, 1 row affectedmysql INSERT INTO t_date VALUES(2, 2018-6-24 15:17:58, NULL, 2018-6-24, 15:18:27, 2018); -- timestamp为空NULL会自动赋当前时间Query OK, 1 row affectedmysql SELECT * FROM t_date;----------------------------------------------------------------------------| id | date_1 | date_2 | date_3 | date_4 | date_5 |----------------------------------------------------------------------------| 1 | 2018-06-24 15:17:58 | 2018-06-24 15:18:14 | 2018-06-24 | 15:18:27 | 2018 || 2 | 2018-06-24 15:17:58 | 2018-06-24 15:35:55 | 2018-06-24 | 15:18:27 | 2018 |----------------------------------------------------------------------------2 rows in setYYYY-MM-DD HH:MM:SS或YY-MM-DD HH:MM:SS格式的字符串。允许“不严格”语法任何标点符都可以用做日期部分或时间部分之间的间割符。例如98-12-31 11:30:45、98.12.31 113045、98/12/31 11*30*45和981231 11^30^45是等价的YYYY-MM-DD或YY-MM-DD格式的字符串。这里也允许使用“不严格的”语法。例如98-12-31、98.12.31、98/12/31和981231是等价的。YYYYMMDD或YYMMDD格式的没有间割符的字符串假定字符串对于日期类型是有意义的。例如19970523和970523被解释为 1997-05-23但971332是不合法的(它有一个没有意义的月和日部分)将变为0000-00-00。YYYYMMDDHHMMSS或YYMMDDHHMMSS格式的数字假定数字对于日期类型是有意义的。例如19830905132800和830905132800被解释为 1983-09-05 13:28:00。YYYYMMDD或YYMMDD格式的数字假定数字对于日期类型是有意义的。例如19830905和830905被解释为1983-09-05。mysql INSERT INTO t_date VALUES(3, 2018624 15^17^58, 2018#6#24 151814, 2018/6/24, 15:18:27, 2018); -- 用不同的标点符号做分隔符Query OK, 1 row affectedmysql SET sql_mode ; -- 设置非严格模式只在该会话中有效Query OK, 0 rows affectedmysql SELECT sql_mode; -- 查看设置是否生效------------| sql_mode |------------| |------------1 row in setmysql INSERT INTO t_date VALUES(4, 20180624151758, 180624151814, 20181324, 15:18:27, 2018); -- 插入20181324时会自动转换为零值前提是要设置非严格模式否则报错Query OK, 1 row affectedmysql SELECT * FROM t_date;----------------------------------------------------------------------------| id | date_1 | date_2 | date_3 | date_4 | date_5 |----------------------------------------------------------------------------| 1 | 2018-06-24 15:17:58 | 2018-06-24 15:18:14 | 2018-06-24 | 15:18:27 | 2018 || 2 | 2018-06-24 15:17:58 | 2018-06-24 15:35:55 | 2018-06-24 | 15:18:27 | 2018 || 3 | 2018-06-24 15:17:58 | 2018-06-24 15:18:14 | 2018-06-24 | 15:18:27 | 2018 || 4 | 2018-06-24 15:17:58 | 2018-06-24 15:18:14 | 0000-00-00 | 15:18:27 | 2018 |----------------------------------------------------------------------------4 rows in setYEAR类型可以用二位数也可以用四位数表示四位字符串范围为1901到2155。四位数字范围为1901到2155。两位字符串范围为00到99。00到69和70到99范围的值被转换为2000到2069和1970到1999范围的YEAR值。两位整数范围为1到99。1到69和70到99范围的值被转换为2001到2069和1970到1999范围的YEAR值。请注意两位整数范围与两位字符串范围稍有不同因为你不能直接将零指定为一个字符串0或00或它被解释为0000。mysql INSERT INTO t_date VALUES(6, 2018-6-24 15:17:58, 2018-6-24 15:18:14, 2018-6-24, 15:18:27, 2100);Query OK, 1 row affectedmysql INSERT INTO t_date VALUES(7, 2018-6-24 15:17:58, 2018-6-24 15:18:14, 2018-6-24, 15:18:27, 2166); -- 超出范围转换为零值Query OK, 1 row affectedmysql INSERT INTO t_date VALUES(8, 2018-6-24 15:17:58, 2018-6-24 15:18:14, 2018-6-24, 15:18:27, 69);Query OK, 1 row affectedmysql INSERT INTO t_date VALUES(9, 2018-6-24 15:17:58, 2018-6-24 15:18:14, 2018-6-24, 15:18:27, 70);Query OK, 1 row affectedmysql SELECT * FROM t_date;------------------------------------------------------------------------------| id | date_1 | date_2 | date_3 | date_4 | date_5 |------------------------------------------------------------------------------| 1 | 2018-06-24 15:17:58 | 2018-06-24 15:18:14 | 2018-06-24 | 15:18:27 | 2018 || 2 | 2018-06-24 15:17:58 | 2018-06-24 15:35:55 | 2018-06-24 | 15:18:27 | 2018 || 3 | 2018-06-24 15:17:58 | 2018-06-24 15:18:14 | 2018-06-24 | 15:18:27 | 2018 || 4 | 2018-06-24 15:17:58 | 2018-06-24 15:18:14 | 0000-00-00 | 15:18:27 | 2018 || 6 | 2018-06-24 15:17:58 | 2018-06-24 15:18:14 | 2018-06-24 | 15:18:27 | 2100 || 7 | 2018-06-24 15:17:58 | 2018-06-24 15:18:14 | 2018-06-24 | 15:18:27 | 0000 || 8 | 2018-06-24 15:17:58 | 2018-06-24 15:18:14 | 2018-06-24 | 15:18:27 | 2069 || 9 | 2018-06-24 15:17:58 | 2018-06-24 15:18:14 | 2018-06-24 | 15:18:27 | 1970 |------------------------------------------------------------------------------8 rows in set (0.00 sec)字符串类型字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。CHAR与VARCHAR类型CHAR表示定长如CHAR(10)表示可以存储10个字符最大长度为255个字符当保存CHAR值时在它们的右边填充空格以达到指定的长度。当检索到CHAR值时尾部的空格被删除掉。在存储或检索过程中不进行大小写转换。VARCHAR表示变长如VARCHAR(10)表示可以存储10个字符最大长度为65535个字符(VARCHAR的最大有效长度由最大行大小和使用的字符集确定实际最大长度为65532个字符)与CHAR不同的是VARCHAR值保存时只保存需要的字符数另加一个字节表示字符的长度(如果长度超过255则使用两个字节)。VARCHAR值保存时不进行填充。当值保存和检索时尾部的空格仍保留。下面的表显示了将各种字符串值保存到CHAR(4)和VARCHAR(4)列后的结果说明了CHAR和VARCHAR之间的差别请注意上表中最后一行的值只适用不使用严格模式时如果MySQL运行在严格模式超过列长度不的值不保存并且会出现错误。定长的磁盘空间比较浪费 但是效率高相反的变长的磁盘空间比较节省 但是效率低BINARY与VARBINARY类型BLOB与TEXT类型通常超过255个字符的数据使用大文本进行存储根据存储的数据格式分为BLOB与TEXTBLOB用来存储二进制数据(少用)TEXT用来存储文字。BLOB是一个二进制大对象可以容纳可变数量的数据。有4种BLOB类型TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。有4种TEXT类型TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型有相同的最大长度和存储需求。ENUM类型ENUM是一个字符串对象其值来自表创建时在列规定中显式枚举的一列值每个枚举值有一个索引(NULL值的索引是NULL)从1开始编号mysql CREATE TABLE t_enum ( -- 创建枚举表gender ENUM(man, woman, unknow));Query OK, 0 rows affectedmysql INSERT INTO t_enum VALUES (man);Query OK, 1 row affectedmysql INSERT INTO t_enum VALUES (woman);Query OK, 1 row affectedmysql INSERT INTO t_enum VALUES (haha); -- 不在枚举定义的值里面不允许插入起到规范数据的作用1265 - Data truncated for column gender at row 1如果在数值上下文中检索一个ENUM值将返回列值的索引。例如你可以这样从ENUM列搜索数值值mysql SELECT gender 0, gender FROM t_enum;--------------------| gender 0 | gender |--------------------| 1 | man || 2 | woman |--------------------2 rows in set由于枚举存储的是数值所以可以直接插入数值mysql INSERT INTO t_enum VALUES (1), (2); -- 直接插入数值Query OK, 2 rows affectedRecords: 2 Duplicates: 0 Warnings: 0mysql SELECT gender 0, gender FROM t_enum;--------------------| gender 0 | gender |--------------------| 1 | man || 2 | woman || 1 | man || 2 | woman |--------------------4 rows in setSET类型集合跟枚举很类似实际存储的是数值(SET最多可以有64个不同的成员)而不是字符串可以把集合看成是多选框枚举看成是单选mysql CREATE TABLE t_set (letter SET (a, b, c, d));Query OK, 0 rows affectedmysql INSERT INTO t_set VALUES (a,b);Query OK, 1 row affectedmysql INSERT INTO t_set VALUES (a,b,c);Query OK, 1 row affectedMySQL用数字保存SET值所保存值的低阶位对应第1个SET成员。如果在数值上下文中检索一个SET值检索的值的位设置对应组成列值的SET成员例如你可以这样从一个SET列检索数值值mysql SELECT letter 0, letter FROM t_set;--------------------| letter 0 | letter |--------------------| 3 | a,b || 7 | a,b,c |--------------------2 rows in set如果你为该列分配一个值9其二进制形式为1001因此第1个和第4个SET值成员a和d被选择结果值为 a,d。对于包含多个SET元素的值当插入值时元素所列的顺序并不重要。在值中一个给定的元素列了多少次也不重要。当以后检索该值时值中的每个元素出现一次根据表创建时指定的顺序列出元素。例如插入值a,d、d,a、a,d,d、a,d,a和d,a,dmysql INSERT INTO t_set VALUES (a,d), (d,a), (a,d,a), (a,d,d), (d,a,d);Query OK, 5 rows affectedRecords: 5 Duplicates: 0 Warnings: 0mysql SELECT letter 0, letter FROM t_set; -- 检索时所有的值将显示为a,d--------------------| letter 0 | letter |--------------------| 3 | a,b || 7 | a,b,c || 9 | a,d || 9 | a,d || 9 | a,d || 9 | a,d || 9 | a,d |--------------------7 rows in set通常情况可以使用FIND_IN_SET()函数或LIKE操作符搜索SET值mysql SELECT * FROM t_set WHERE FIND_IN_SET(a,letter) 0; -- 表示找出t_set包含a的成员--------| letter |--------| a,b || a,b,c || a,d || a,d || a,d || a,d || a,d |--------7 rows in setmysql SELECT * FROM t_set WHERE letter LIKE %a%; -- 所有set中包含a的成员甚至是在另一个SET成员的子字符串中--------| letter |--------| a,b || a,b,c || a,d || a,d || a,d || a,d || a,d |--------7 rows in set
