MySQL如何选择数据类型

2019-02-16 07:28:10 MySQL

MySQL

选择合适的数据类型

几个简单的原则：

a 更小的通常更好
b 简单就好。例如多用内建类型而非字符串存日期时间；用整形存ip
c 尽量避免null，尤其是索引列，会在排序的时候产生一些效率问题，例外是innodb引擎使用单独的位（bit）存null，对系数数据有很好的空间效率。

整形

tinyint, smallint, mediumint, int, bigint 几种数据类型所占空间分别是8bit, 16bit, 24bit, 36bit, 64bit
范围是-2的n-1次方到2的n-1次方减1
可选unsigned属性，不许负值，如此以来存储负数的空间用来存正数，可以使正数上限提高近1倍
指定宽度只是限制交互工具，如命令行客户端等显示字符的个数，对于存储和计算来说int(1) int(20)是相同的

实数（带小数部分的数字）

不只为存小数部分，也可用decimal存比bigint大的正数。
float和double类型支持使用标准浮点运算进行近似计算。
decimal类型用于存储精确的小数。5.0版本后支持精算。其将数字打包保存到一个二进制字符串，每四个字节存九个数字，例如decimal(18,9)小数点两边各9数字，共9字节，前后各4，小数点占1个。最多允许65个数字。
float和double分别是占4字节和8字节，内部使用double进行浮点计算。
对于小数精算时使用decimal，例如财务数据。但是数据量过大，可以用bigint带起，将需存的货币单位乘以相应倍数即可。

字符串类型

varchar的选择条件
- 字符串列最大长度比平均长度大很多
- 列的更新很少，所以碎片不是问题
- 使用了像utf8这样复杂的字符集，每个字符都用不同字节数存储
char
- 会根据需要采用空格进行填充以便比较
- 适合短字符串或所有值接近一个长度，例如md5值就是定长
- 对于常变数据char优于varchar，因为定长char无碎片
- 短列char空间利用率高，例如char(1)占1byte 而varchar(1)需要2byte，因为有1byte存字符串长度
- varchar(5)跟varchar(200)存'hello'空间开销一样，但是短列内存消耗更少

日期和时间

timestamp 4字节 datetime 8字节
timestamp 与时区有关，datetime与时区无关
timestamp区间1970到2038，但是我曾测试可以插入0000 00：00：00，datetime1001年到9999年
timestamp默认not null，但并不是网上说的不能设置null，插入时会默认当前时间，但是这些功能可以通过default current_timestamp, on update current_timestamp等实现插入或者更新保存当前时间，并不是timestamp才可以
关于这两种类型的比较详情请见这里

补充

varchar类型是用于存储可变长的字符串，比定长类型更节省空间，它仅使用必要的空间。
所谓varchar(5)跟varchar(200)存 ‘hello’ 空间开销一样，可以看上面的定义‘仅使用必要空间’，所以说都是存5个字符，只占用5个字符的空间。
而在内存的分配过程中会根据varchar后面的数字分配固定大小的内存空间，这样就导致了varchar(200)分配了多余的内存，这样在使用内存临时表等情况的时候会有所影响。
不要把int后面的数字跟varchar后面的数字混为一谈，int后面数字是指宽度，举例来说1到9的宽度都是1,10到99的宽度都是2，56345的宽度是5，这个只影响客户端交互，mysql手册上说这个是显示宽度，而实际不会跟varchar那样选择必要空间存储，也就是说int(1)跟int(20)都是需要32B的空间的，计算上也是这样，而且并不是说int(1)就不能存10, 手册上表明只要此类型可以存下就能存，与后面的显示长度无关。

注：5.0之前varchar后面数字是字节数，之后是字符个数，如果是之前的字节的话，能存多少字符就不好说了，因为还跟字符集有关。

欢迎小伙伴们在下方评论区留言 ~ O(∩_∩)O
文章对我有帮助, 点此请博主吃包辣条 ~ O(∩_∩)O

AdSense Anaconda Android apache API apt Auth AWS B-tree Bandwagon Blog bower brew bytes Caffe Catalina certbot Charles cloudcone Composer conda CoreML CPU crontab CSS csv Cuda cv2 datetime Digitalocean DNS Docker Docker-Compose Eloquent Excel export Flask FTP GET Git GitHub GitLab Gmail GoDaddy Google GTM hash Homebrew Homestead HTML http HTTPS IDEA image imagemagick imagick imgick import InnoDB ios iou iPhone ISO8601 iTerm2 Java JavaScript JPG JS Keras Laravel Laravel-Admin lazyload Linux list Livewire lnmp load logs Lravel Mac Markdown matplotlib md5 mix MobileNet Mojave mongo MongoDB MySQL Namesilo Nginx Node npm numpy Nvidia Nvidia-Docker onevps OpenCV Openpose openpyxl oss Outline parse PayPal PHP php-fpm PhpStorm PHP扩展 PIL Pillow pip PNG POST Protobuf PyCharm pyenv pymongo Python Python,人工智能,机器学习,VOC,xml Queue Redis requests RGB Sanctum save selenium SEO Shadowsock Shadowsocks ShadowsocksR simplemde Spring Boot SQLServer ssd SSH ssl SSL证书 SSR str Sublime sudo swap Swift Tensorflow TensorflowLite Terminal Terminator timestamp Ubuntu urllib UTC v2ray Valet Validation Validator VienBlog virtualenvs VPN VPS Vultr Web Windows WordPress Xcode xlsx yaml YAPI YUV zip zmq zsh 上网下载图片主从同步云主机云存储云开发云服务器人工智能优化优惠码伪原创作弊与反作弊免费ss账号免费提现切片前端加密协议博客友链双击事件后台运行后端命令国内镜像源图标图片操作图片转换域名多身份认证大小写转换姿态检测安卓模拟器安装定时任务定时执行密码密钥导出导入小程序码延迟加载异常微信微信小程序快捷方式慢查询懒加载提现搜索引擎搬瓦工搭梯子教程数据库数据重复文件上传无法登录日志日期时区时间时间戳服务器机器学习权限梯子模拟浏览器港版支付宝漏洞爬虫生活服务用户管理病毒登录目标检测科学上网系统升级索引组件组件开发编辑器自动付款自定义组件英文伪原创计划任务计算机视觉订阅通知认证语法读写分离远程连接配置文件重定向错误异常错误提示队列阿里云香港香港手机号

亲情非友情链接