Mysql基本功-数据类型

2019-01-30 本文已影响0人 166702df7928

基本数据类型-数值类型

概览

括号内数字表示显示的宽度.zerofill 起到补位作用.默认都是有符号的

CREATE TABLE `table_type_of_number` (

`k1` tinyint(2) NOT NULL AUTO_INCREMENT COMMENT '自增,1字节,默认有符号范围(-128,127).无符号范围(0,255)',

`k2` tinyint(2) unsigned zerofill NOT NULL DEFAULT '00' COMMENT '1字节,默认有符号范围(-128,127).无符号范围(0,255),宽度不足2则在数字前面补位0', //最大能存储 3位整数

`k3` smallint(2) NOT NULL DEFAULT '0' COMMENT '2字节,默认有符号范围(-32 768，32 767).无符号范围(0，65 535)’, //最大能存储 5位整数

`k4` mediumint(3) NOT NULL DEFAULT '0' COMMENT '3字节,默认有符号范围(-8 388 608，8 388 607).无符号范围(0，16 777 215)’,//最大能存储 8位整数

`k5` int(3) NOT NULL DEFAULT '0' COMMENT'4字节,默认有符号范围(-2147 4836 48，2147 4836 47).   无符号范围(0，4294967295)’, //最大能存储 10位整数

`k6` bigint(3) NOT NULL DEFAULT '0' COMMENT '8字节,默认有符号范围(-9[233 372 036 854 775](tel:233%20372%20036%20854%20775)808，9[223 372 036 854 775](tel:223%20372%20036%20854%20775)807).无符号范围(0，18[446 744 073 709 551](tel:446%20744%20073%20709%20551)615)’, //最大能存储 19位整数

`k7` float(10,2) NOT NULL DEFAULT '0.00' COMMENT '4字节,精度不准,短补位长截断,整数位数+小数位数=10,补位后超位则报错',

`k8` double(10,2) NOT NULL DEFAULT '0.00' COMMENT '8字节,精度不准,短补位长截断,整数位数+小数位数=10,补位后超位则报错',

  `k9` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT 'if(10>2) 字节=10+2,否则字节=2+2.保留2位小数,短补位长截断,整数位数+小数位数=10,补位后超位则报错',

  PRIMARY KEY (`k1`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='数值类型表’

float double decimal的区别

单精度浮点数在机内占4个字节，用32位二进制描述。

双精度浮点数在机内占8个字节，用64位二进制描述。

MySQL中存在float,double等非标准数据类型，也有decimal这种标准数据类型。

其区别在于，float，double等非标准类型，在DB中保存的是近似值，而Decimal则以字符串的形式保存数值。

float，double类型是可以存浮点数（即小数类型），但是float有个坏处，当你给定的数据是整数的时候，那么它就以整数给你处理。这样我们在存取货币值的时候自然遇到问题，我的default值为：0.00而实际存储是0，同样我存取货币为12.00，实际存储是12。

幸好mysql提供了两个数据类型：decimal，这种数据类型可以轻松解决上面的问题：decimal类型被 MySQL 以同样的类型实现，这在 SQL92 标准中是允许的。他们用于保存对准确精度有重要要求的值，例如与金钱有关的数据。

decimal类型 (定点数)

//创建数据表

CREATE TABLE IF NOT EXISTS `decimal_test`(num DECIMAL(5,3));

//批量插入多条数据

INSERT INTO `decimal_test`(k1,k2) VALUES(1,2),(3,4);

INSERT INTO `decimal_test`(num) VALUES(12.345),(123.45),(1.2);

//decimal数据测验

mysql> INSERT INTO `decimal_test`(num)VALUES(1.2345);

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

mysql>  INSERT INTO `decimal_test`(num)VALUES(12.345);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `decimal_test`(num)VALUES(123.45);  //小数部分未满3位,补0,为,123.450 超出上限位数5,故报错

ERROR 1264 (22003): Out of range value for column 'num' at row 1

mysql>  INSERT INTO `decimal_test`(num) VALUES(1.2);

Query OK, 1 row affected (0.00 sec)

mysql> select * from decimal_test ;

+--------+

| num    |

+--------+

|  1.235 |   //`ok,小数点保留3位,超则四舍五入截断处理`

| 12.345 |   //ok,符合标准

|  1.200 |   //ok,小数点保留3位,少则添0补位

+--------+

> float类型  (浮点数)

//创建数据表

CREATE TABLE IF NOT EXISTS `float_test`(num float(10,2));

mysql> CREATE TABLE IF NOT EXISTS `float_test`(num float(10,2));

Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO `float_test`(num)VALUES(76547654.23);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `float_test`(num)VALUES(1234.12);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `float_test`(num)VALUES(1234.123);

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO `float_test`(num)VALUES(1234.1);

Query OK, 1 row affected (0.00 sec)

mysql> select * from  float_test;

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

| num         |

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

| 76547656.00 |   //数据一多,整数位也保留不准确了 , 且小数位抹去了..

|     1234.12 |   //ok,达标

|     1234.12 |  //ok,超则小数四舍五入截断

|     1234.10 |  //ok,少则补位

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

总结:会进行四舍五入, 并且会有误差!!!!!!

double类型 (浮点数)

经过测试, 情况同float

更改列名

CREATE TABLE IF NOT EXISTS `float_test`(num float(5,3));

alter table 表名 add 列名 int(1) NOT NULL DEFAULT'0' COMMENT'0=未执行，1=已执行';    增加key（表的列）和key的属性

alter table 表名 add KEY keyname(列名1，列名2，列名n); KEY可以加快查询速度，不会影响插值

alter table camp_tmp change camp_budget camp_budget decimal(5,6),not null,default '0',comment '注释';   //修改\

mysql> alter table float_test change num num double(5,3);

Query OK, 0 rows affected (0.04 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table float_test;

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

| Table      | Create Table                                                                                        |

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

| float_test | CREATE TABLE `float_test` (

  `num` double(5,3) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

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

INSERT INTO `float_test`(num)VALUES(123.12);//补位后,总长度大于5了,报错 [ decimal float(5,3)和double(5,3) 都是这个情况]

一些异常情况

mid超长,导致异常截断,需要用string 而不是用int数据类型的

(double)mid

(float)mid

存入mysql表中 mid

$mid = '9223372036854775807'; //9223372036854775807是64位系统下, int能存储的最大值

var_dump($mid); //string(19) "9223372036854775807"

var_dump((int)$mid); //int(9223372036854775807)

$mid = '922337203685477580888123123123';

var_dump($mid); //string(30) "922337203685477580888123123123"

var_dump((int)$mid); //int(9223372036854775807) 超长则会被异常截断

基本数据类型-字符串类型

概览

Value	CHAR(4)	Storage Required	VARCHAR(4)	Storage Required
''	' '	4 bytes	''	1 byte
'ab'	'ab '	4 bytes	'ab'	3 bytes
'abcd'	'abcd'	4 bytes	'abcd'	5 bytes
'abcdefgh'	'abcd'	4 bytes	'abcd'	5 bytes

以上总计:

1.超长,会被截断 ->不严谨, 得看具体当前的sql_mode

2.char,固定长度(value值为空也固定),处理速度快,但是浪费存储空间

3.varchar 实际存储字节数 = 插入的字符数+本身字段占用数

不同存储引擎选择合适的数据类型

1.myisam引擎, 建议使用固定长度的数据列代替可变长度的数据列; (myisam的静态表)

2.innodb引擎,建议varchar类型,因为内部的存储格式没有区分固定/可变长度列,所以用varchar对于存储总量和磁盘io比较友好的

CREATE TABLE `char_varchar_test` (

`k1` char(4) NOT NULL DEFAULT '0' COMMENT '0-255字节,定长字符串,此时占M字节,长截断,短则尾部补位空格',

`k2` varchar(4) NOT NULL DEFAULT '0' COMMENT '0-65535 字节,不定长字符串,此时占(1,1+M)字节,长截断,短则尾部补位空格'

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='字符串类型表-char,varchar测试';

mysql> desc char_varchar_test;

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

| Field | Type       | Null | Key | Default | Extra |

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

| k1    | char(4)    | NO   |     | 0       |       |

| k2    | varchar(4) | NO   |     | 0       |       |

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

2 rows in set (0.01 sec)

mysql> insert into char_varchar_test set k1='',k2='';  //此时k1固定占4字节,此时k2占1字节

Query OK, 1 row affected (0.00 sec)

mysql> insert into char_varchar_test set k1='ab',k2='ab'; //此时k1固定占4字节,此时k2占1+2=3字节

Query OK, 1 row affected (0.00 sec)

mysql> insert into char_varchar_test set k1='abcd',k2='abcd'; //此时k1固定占4字节,此时k2占1+4=5字节

Query OK, 1 row affected (0.00 sec)

//期望,k1,k2都能截断成abcd ,则此时k1固定占4字节,此时k2占1+2=3字节 ; 实际上,超长报错了...

mysql> insert into char_varchar_test set k1='abcdefgh',k2='abcdefgh';  

ERROR 1406 (22001): Data too long for column 'k1' at row 1

关于超长字符, 到底是报错还是截断的问题说明

----------------------------------------------------------------------+
| user  | CREATE TABLE "user" (

  "id" int(11) NOT NULL AUTO_INCREMENT,

  "mobile" varchar(11) DEFAULT NULL,

  "last_login_time" datetime DEFAULT NULL,

  PRIMARY KEY ("id"),

  UNIQUE KEY "mobile" ("mobile")

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |

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

1 row in set (0.00 sec)

mysql> insert into user set mobile='18624090272';

Query OK, 1 row affected (0.01 sec)

mysql> select * from user;

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

| id | mobile      | last_login_time     |

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

|  1 | 15012345678 | 2019-01-28 17:23:45 |

|  2 | 15012345679 | 2019-01-28 17:24:56 |

|  4 | 15012345671 | 2019-01-28 17:25:40 |

|  5 | 18624090272 | NULL                |

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

4 rows in set (0.00 sec)

mysql> insert into user set mobile='18624090272123';

ERROR 1406 (22001): Data too long for column 'mobile' at row 1

mysql> insert into user set mobile='186240902721';

ERROR 1406 (22001): Data too long for column 'mobile' at row 1

mysql> insert into user set mobile='18624090272,';

ERROR 1406 (22001): Data too long for column 'mobile' at row 1

mysql> insert into user set mobile='1862409,1842409';  //`超长的字符不为空格, 严格模式下, 只会报error, 且不允许插入字符;`

ERROR 1406 (22001): Data too long for column 'mobile' at row 1

mysql> insert into user set mobile='18624090272 ';  

ERROR 1062 (23000): Duplicate entry '18624090272' for key 'mobile'

mysql> insert into user set mobile='18624090273 '; `超长的字符为空格, 严格模式下, 只会报warning;`

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

//查看当前连接会话的sql模式：

mysql> select @@session.sql_mode;

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

| @@session.sql_mode                                                                                                                                                       |

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

| PIPES_AS_CONCAT,ANSI_QUOTES,NO_AUTO_VALUE_ON_ZERO,`STRICT_TRANS_TABLES`,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

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

1 row in set (0.00 sec)

mysql> show variables like "sql_mode"; //???

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"sql_mode"' at line 1

mysql>

mysql>

//查看全局sql_mode设置：  (**发现有STRICT_TRANS_TABLES, 或者cat /etc/mysql.cnf  也会看见sql_mode**)

mysql> select @@global.sql_mode;

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

| @@global.sql_mode                                                                                                                                                        |

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

| PIPES_AS_CONCAT,ANSI_QUOTES,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

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

1 row in set (0.01 sec)

关于 STRICT_TRANS_TABLES

如果不能将给定的值插入到事务表中，则放弃该语句。对于非事务表，如果值出现在单行语句或多行语句的第1行，则放弃该语句。本节后面给出了更详细的描述。

STRICT_TRANS_TABLES

设置它，表示启用严格模式。

注意 STRICT_TRANS_TABLES 不是几种策略的组合，单独指 INSERT、UPDATE出现少值或无效值该如何处理:

1.前面提到的把 ‘’ 传给int，严格模式下非法，若启用非严格模式则变成0，产生一个warning

2.Out Of Range，变成插入最大边界值

3.A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition

可以禁用STRICT_TRANS_TABLES和STRICT_ALL_TABLES。这允许自动截断插入的字符串。

//修改当前session的sql_mode, 去掉STRICT_TRANS_TABLES

set session sql_mode= 'PIPES_AS_CONCAT,ANSI_QUOTES,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

//重试一个超长的字符

mysql> insert into user set mobile='1862409,1842409';

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

mysql> select * from user;

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

| id | mobile      | last_login_time     |

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

|  1 | 15012345678 | 2019-01-28 17:23:45 |

|  2 | 15012345679 | 2019-01-28 17:24:56 |

|  4 | 15012345671 | 2019-01-28 17:25:40 |

|  5 | 18624090272 | NULL                |

|  7 | 18624090273 | NULL                |

|  8 | 1862409,184 | NULL                |    //超长的字符不为空格, 非严格模式下, 只会报warning, 且允许插入字符;

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

关于text类型的使用说明

//关于delete操作会带来的一些问题

delete操作后,会在数据表中留下很大的空洞,以后填入空洞的记录在插入的性能上会有影响,所以要定期使用optimize table进行碎片整理…,

所以含有text和blob字段的,如果经常做删除和修改记录的操作要定时执行optimize table进行碎片整理

//关于合成索引的使用

create table t (id varchar(100), hash_value varchar(40);

insert into t values(1,"beijing",md5(context))

id    context    hash_value

1    beijing       erd33334eeeeeee...(hash值)

然后根据hash值,进行精确匹配; 这种技术只能用于精确匹配,一定程度减少了i/o,提高了查询效率

//text 不允许设置default!! 否则建表失败

`k3` text NOT NULL DEFAULT '' 'COMMENT '0-65 535字节,不允许设默认值吧',

解决方案:

我在dba建表的时候也是不让设置默认值!!

解决的方法：

修改my.ini配置文件，取消严格控制strict模式。

/etc/my.cnf

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

把STRICT_TRANS_TABLES 严格校验屏蔽 , 然后重启

ps -ef|grep mysql

root     25360  1221  0 10:10 pts/6    00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/data0/mysql --socket=/data0/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql

mysql[25593 25360](tel:25593%2025360)4 10:10 pts/6    00:00:03 /usr/sbin/mysqld --basedir=/usr --datadir=/data0/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/data0/mysql/mysql.sock

root[31549 10906](tel:31549%2010906)0 10:11 pts/5    00:00:00 grep mysql

把这两个mysql服务都kill掉 然后执行上面的命令 重启

//按照规定建表,其中text 不允许设置default值,但是可以写NOT NULL,你也可以不写.

mysql> CREATE TABLE `text_test` (

->   `k1` text COMMENT '0-65 535字节’,//varchar(4096) ‘796580857589710ec74bf27479ba4e2e,796580857589710ec74bf27479ba4e2e’  这种更好点...  占用的字节少

    ->   `k2` text NOT NULL COMMENT '0-65 535字节',

    ->   `k4` tinytext COMMENT '相关词详情',

->   `k5` mediumtext COMMENT '[0-16 777 215](tel:0-16%20777%20215)字节',

->   `k6` longtext COMMENT ' 0-4[294 967 295](tel:294%20967%20295)字节'

    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='字符串类型表-tinytext,text,mediumtext,longtext测试';

Query OK, 0 rows affected (0.01 sec)

//关于text矛盾点

mysql> insert into text_test (k1,k2)
values ('[{"ad_type_num":1,"num":10},{"ad_type_num":2,"num":11}]','[{"ad_type_num":1,"num":10},{"ad_type_num":2,"num":11}]');

Query OK, 1 row affected (0.00 sec)


mysql> insert into text_test set k1 = '[{"ad_type_num":1,"num":10},{"ad_type_num":2,"num":11}]';

ERROR 1364 (HY000): Field 'k2' doesn't have a default value

mysql> alter table text_test change k2 k2 text NOT NULL DEFAULT '' COMMENT '0-65 535字节';

ERROR 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 'k2' can't have a default value

/etc/my.cnf

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

把STRICT_TRANS_TABLES 严格校验屏蔽 , 然后重启

ps -ef|grep mysql

root     25360  1221  0 10:10 pts/6    00:00:00 /bin/sh /usr/bin/mysqld_safe--datadir=/data0/mysql --socket=/data0/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql

mysql    25593 25360  4 10:10 pts/6    00:00:03 /usr/sbin/mysqld--basedir=/usr --datadir=/data0/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/data0/mysql/mysql.sock

root     31549 10906  0 10:11 pts/5    00:00:00 grep mysql

把这两个mysql服务都kill掉 然后执行上面的命令 重启

//关于text使用总结

1.text不允许设置default,如果想设置为default,则需要修改my.ini配置文件，取消严格控制strict模式

2.text允许设置为NOT NULL,同时,允许此时不必写default.

3.基于第二点, 假设例子 k1 text , k2 text NOT NULL 则insert时候,必须把k2也insert 否则就见<text矛盾点>

基本数据类型-时间类型

概览

CREATE TABLE `test_date` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

`time_stamp` timestamp NOT NULLDEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMPCOMMENT '更新时间',

  `date_time` datetime DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='日期和时间类型表';

insert into test_date (time_stamp,date_time) values ('2018-06-01 10:00:00','2018-06-01 10:00:00');

mysql> select * from test_date ;

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

| id | time_stamp          | date_time           |

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

|  1 | 2018-06-01 10:00:00 | 2018-06-01 10:00:00 |

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

insert into test_date (date_time) values ('2018-06-02 10:00:00');  //timestamp有默认值...跟着时区定的

mysql> select * from test_date ;

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

| id | time_stamp          | date_time           |

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

|  1 | 2018-06-01 10:00:00 | 2018-06-01 10:00:00 |

|  2 | 2018-09-05 11:11:07 | 2018-06-02 10:00:00 |

+----+---------------------+——————————+

字段	字节	是否跟当前时区变化	范围	格式	精确度
datetime	`8`	否	大 '1000-01-01 00:00:00' ~'9999-12-31 23:59:59'	2018-06-01 10:00:00	秒
timestamp	`4`	否	小 '1970-01-01 00:00:01’ ~ '2038-01-19 03:14:07'	2018-06-01 10:00:00	秒
date	3	-	1000-01-01 ~ 9999-12-31	YYYY-MM-DD	秒
time	3	-	'-838:59:59’ ~'838:59:59'	HH:MM:SS	时间值
year	1	-	1901 ~ 2155	YYYY	年份