MySQL：数据类型

数据类型

• 1. 字符串类型
• 2. 整数类型
• 3. 定点数类型和浮点数类型
• 4. 布尔类型
• 5. 枚举和集合类型
• 6. 日期和时间类型
• 7. Blob类型
• 8. JSON类型

  

  字符串类型、数字类型、日期和时间类型、存放二进制的数据类型、存放地理数据的类型。

  1. 字符串类型

  字符串类型也可以用来存储邮编，电话号码这样的特殊的数字型文本数据，因为邮编电话号码等不会用来做数学运算而且常常包含‘-’或括号等。

  类型	含义	备注
  CHAR()	固定长度字符串	一般用于固定格式字符串
  VARCHAR()	可变字符串	VARCHAR(50)记录短文本、VARCHAR(255)记录长文本。最多存储64KB， 65535个字符（英文），超过部分会截断
  MEDIUMTEXT	最大存储16MB	适合存储JSON对象、CS视图字符串、中短长度的书
  LONGTEXT	Max:4GB	适合存储书籍和以年记的日志
  TINYTEXT	Max:255Bytes
  TEXT	Max:64KB	最好用VARCHAR，因VARCHAR可以使用索引

  所有这些字符串类型都支持国际字符，其中：

  • 英文字符占1个字节
  • 欧洲和中东语言字符占2个字节
  • 像中日这样的亚洲语言的字符占3个字节

    所以，如果一列数据的类型为 CHAR(10)，MySQL会预留30字节给那一列的值。

    2. 整数类型

    bit(1)-B(8);B(1)-KB(1024)-MB(1204KB)-GB(1024MB)-TB(1024GB)

    K=1024=2的10次方, M=1024K=2的20次方

    整数类型	占用储存	记录数字范围
    TINYINT	1B	[-128, 127]
    UNSIGNED TINYINT	1B	[0, 255]
    SMALLINT	2B	[-32K, 32K]
    MEDIUMINT	3B	[-8M, 8M]
    INT	4B	[-2B, 2B]
    BIGINT	8B	[-9Z, 9Z]

    3. 定点数类型和浮点数类型

    类型	名称	占用存储	意义
    定点数	DECIMAL(x,x)DEC/NUMERIC/FIXED	根据需求分配	DECIMAL(9,2),共9位，小数点2位，整数部分最多7位
    浮点数	FLOAT	4B	单精度浮点数，科学计数法
    浮点数	DOUBLE	8B	双精度浮点数，科学计数法

    • 如果需要记录精确数字，比如【货币金额】，就是用 DECIMAL 类型
    • 如果要进行【科学计算】，要处理很大或很小的数据，而且精确值不重要的话，就用 FLOAT 或 DOUBLE。

      4. 布尔类型

      TRUE/FALSE对应整数1/0

      5. 枚举和集合类型

      希望某个字段从固定的一系列值中取值，我们就可以用到 ENUM() 和 SET() 类型，前者是取一个值，后者是取多个值。

      1. ENUM()从固定一系列值中取一个值.

         案例：sql_store.products（产品表）里多一个size（尺码）字段，取值为 small/medium/large 中的一个，可以打开产品表的设计模式，添加size列，数据类型设置为 ENUM(‘small’,‘medium’,‘large’)，然后apply。

      ALTER TABLE `sql_store`.`products`
      ADD COLUMN `size` ENUM('small', 'medium', 'large') NULL AFTER `unit_price`;
      

      1. SET():从固定一系列值中取多个值而非一个值。

      Note: 讲解 ENUM 和 SET 只是为了眼熟，最好不要用这两个数据类型，问题很多：

      1. 修改可选的值（如想增加一个’extra large’）会重建整个表，耗费资源;
      2. 想查询可选值的列表或者想用可选值当作一个下拉列表都会比较麻烦；
      3. 难以在其它表里复用，其它地方要用只有重建相同的列，之后想修改就要多处修改，又会很麻烦。

      解决方法：像这种某个字段从固定的一系列值中取值的情况，不应该使用 ENUM 和 SET 而应该用这一系列的值另外建一个“查询表” (lookup table)。应 该 另 外 建 一 个 size 尺 码 表 ， 就 像 sql_invoicing 里 为 支 付 方 式 专 门 建 了 一 个payment_methods 表一样。这样就解决了上面的所有问题，既方便查询可选值的列表和作为下拉选项，也方便复用和更改。

      6. 日期和时间类型

      类型	含义	备注
      DATE	有日期没时间
      TIME	有时间没日期
      YEAR	存储四位的年份
      DATETIME	包含日期和时间	8B
      TIMESTAMP	时间戳，常用来记录一行数据的插入和最后更新时间	占4B，最晚记录2038，称为“2038问题”

      7. Blob类型

      Blob 类型来储存大的二进制数据，包括PDF，图像，视频等等几乎所有的二进制的文件。通常应该将二进制文件存放在数据库之外，关系型数据库是设计来专门处理结构化关系型数据而非二进制文件的。如果将文件储存在数据库内，会有如下问题：数据库的大小将迅速增长、备份会很慢、性能问题和需要额外的读写图像的代码。

      类型	含义
      TINYBOLB	255B
      BLOB	65KB
      MEDIUMBIOB	16MB
      LONGBIOB	4GB

      尽量别用数据库来存文件，除非这样做确实有必要而且上面这些问题都已经考虑过了。

      8. JSON类型

      MySQL还可以储存 JSON 文件，JSON 是 JavaScript Object Notation（JavaScript 对象标记法）的简称。简单讲，JSON 是一种在互联网上储存和传播数据的简便格式，JSON 在网络和移动应用中被大量使用，多数时候你的手机应用会以 JSON 形式向后端传输数据。

      语法结构：

      {
      "key": value
      }
      

      1 . JSON 用大括号 {} 表示一个对象，里面有多对键值对

      2. 键 key 必须用引号包裹（而且必须是双引号，不能用单引号）

      3. 值 value 可以是数值，布林值，数组，文本， 甚至另一个对象（形成嵌套 JSON 对象）

      案例：

      注意这里每件产品的独特属性的种类是不一样的，如衣服是颜色和尺码，而电视机是的重量和尺寸，把所有可能的属性都作为不同的列添加进表是很糟糕的设计，因为每个商品都只能用到所有这些属性的一部分，相反，通过增加一列 JSON 类型的 properties 列，我们可以利用 JSON 里的键值对很方便的储存每个商品独特的属性。

      1. 增

         添加一列属性，设置数据类型为JSON，默认值为NULL。

         方法1：采用JSON的标准格式：

      UPDATE products
      SET properties = '
      {
          "dimensions":[1,2,3],
          "weight":10,
          "manufacturer":{"name":"sony"}
      }'
      WHERE product_id = 1;
      

      方法2：采用针对JSON的内置函数

      UPDATE products
      SET properties = JSON_OBJECT(
          'weight',  10,
          'dimensions',  JSON_ARRAY(1,2,3),
          'manufacturer', JSON_OBJECT('name','sony')
      )
      WHERE product_id = 1;
      

      1. 查

         查询 JSON 对象里的特定键值对，这是将一列设为 JSON 对象的优势所在，如果 properties 列是字符串类型如 VARCHAR 等，是很难获取特定的键值对的。

         方法1：采用JSON_EXTRACT()函数，其中第一个参数指明JSON对象，第二个参数用单引号包裹的路径，路径中$表示当前对象，点操作符.表示对象的属性

      SELECT product_id, JSON_EXTRACT(properties, '$.weight') AS weight
      FROM products
      WHERE product_id = 1;
      

      方法2：使用列路径操作符 -> 和 ->>，后者可以去掉结果外层的引号

      SELECT 
          product_id, 
          properties -> '$.weight' AS weight,
          properties -> '$.dimensions' AS dimension,
          properties -> '$.dimensions[0]' AS dimension_length,
          -- JSON对象的索引从0开始，MySQL的字符串索引从1开始
          properties -> '$.manufacturer.name' AS manufacturer,
          properties ->>'$.manufacturer.name' AS manufacturer
          -- 用->> 来消除结果显示中的引号
      FROM products
      WHERE product_id = 1;
      

      • Note: 通过路径操作符来获取 JSON 对象的特定属性不仅可以用在 SELECT 选择语句中，也可以用在 WHERE 筛选语句中.
        1. 改

           如果我们是要重新设置整个JSON对象就用前面增里讲到的JSON_OBJECT()函数，但如果是想修改已有JSON对象里的部分属性，就要用JSON_SET()函数。

           同样可以采用重新定义来更改其中的数据。

        UPDATE products
        SET properties = JSON_SET(
            properties,
            '$.weight', 20,
            '$.age', 10  -- 增加age属性
        )
        WHERE product_id = 1;
        

        • 语法规则：JSON_SET（）函数的第一个参数为需要修改的属性列
          1. 删

             可以用JSON_REMOVE()函数实现对已有JSON对象特性属性的删除，原理和JSON_SET()一样。

          UPDATE products
          SET properties = JSON_REMOVE(
              properties,
              '$.weight',
              '$.age'  -- 删除age属性
          )
          WHERE product_id = 1;