锐客网

  1. 首页 > it技术 > >

Mysql基本功-数据类型

基本数据类型-数值类型 概览 括号内数字 表示 显示的宽度.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 1mysql>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 * fromfloat_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: 0Duplicates: 0Warnings: 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
【Mysql基本功-数据类型】以上总计:
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 1mysql> insert into user set mobile='186240902721'; ERROR 1406 (22001): Data too long for column 'mobile' at row 1mysql> insert into user set mobile='18624090272,'; ERROR 1406 (22001): Data too long for column 'mobile' at row 1mysql> insert into user set mobile='1862409,1842409'; //`超长的字符不为空格, 严格模式下, 只会报error, 且不允许插入字符; `ERROR 1406 (22001): Data too long for column 'mobile' at row 1mysql> 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 1mysql>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))idcontexthash_value1beijingerd33334eeeeeee...(hash值)然后根据hash值,进行精确匹配; 这种技术只能用于精确匹配,一定程度减少了i/o,提高了查询效率//text 不允许设置default!! 否则建表失败`k3` text NOT NULL DEFAULT '' 'COMMENT '0-65 535字节,不允许设默认值吧',

解决方案:
我在dba建表的时候也是 不让设置默认值!!
解决的方法：
修改my.ini配置文件，取消严格控制strict模式。/etc/my.cnfsql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES把STRICT_TRANS_TABLES 严格校验屏蔽 , 然后重启ps -ef|grep mysqlroot2536012210 10:10 pts/600: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=mysqlmysql[25593 25360](tel:25593%2025360)4 10:10 pts/600: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.sockroot[31549 10906](tel:31549%2010906)0 10:11 pts/500: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 valuemysql> 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.cnfsql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES把STRICT_TRANS_TABLES 严格校验屏蔽 , 然后重启ps -ef|grep mysqlroot2536012210 10:10 pts/600: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=mysqlmysql25593 253604 10:10 pts/600: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.sockroot31549 109060 10:11 pts/500: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 否则就见
基本数据类型-时间类型 概览
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 年份

    推荐阅读


    上一篇：Java|Java 排序遇到的神坑，我替你踩了！

    下一篇：幸福亲子日记第56篇《和善坚定规则》