精华内容
下载资源
问答
  • 微信企业号:员工信息自定义字段

    千次阅读 2017-04-14 11:29:33
    包括:姓名、头像、性别、所属部门、账号、手机、邮箱、职位和微信,这些字段员工的基本信息,不能删除。     如果需要增加其他字段信息,则需要先在企业微信号设置。   二、增加自定义字段 1、在...
    
    一、通讯录员工基本信息

    包括:姓名、头像、性别、所属部门、账号、手机、邮箱、职位和微信,这些字段是员工的基本信息,不能删除。

     

     

    如果需要增加其他字段信息,则需要先在企业微信号中设置。

     

    二、增加自定义字段

    1、在通讯录中选择【扩展字段】,点击右边的箭头进行维护。

     

    2、点击【添加】按钮,既可以增加普通字段,也可以增加唯一性字段。以下为增加【出生日期】为例。新增字段可以删除,基本字段不能删除,但可以设置为不显示。

     

     

    3、建立子定义字段后,就可以维护员工信息了,维护界面就增加了【出生日期】信息。注意:这个字段不进行数据类型的判断,需自行处理。

     

     

    三、系统读取用户的信息,包括自定义字段。

    1、Https请求方式: GET

    https://qyapi.weixin.qq.com/cgi-bin/user/list?access_token=ACCESS_TOKEN&department_id=DEPARTMENT_ID&fetch_child=FETCH_CHILD&status=STATUS

    返回信息如下,包括errcodeerrmsguserlistuserlistextattr就是自定义字段的内容。

     

     

    2、C#示例

    先定义员工信息类:

     

    读取员工信息:

     

    获取自定义字段信息,写入数据库

     

     

    更多微信开发的文章请访问 http://www.xici.net/b1261440 

     

     
    展开全文
  • MySQL08 DQL之约束(对表中字段的限定)

    千次阅读 2019-02-23 21:55:00
    对表的数据(字段)进行限定,保证数据的正确性、有效性和完整性。非法的数据不能够添加到数据库 分类: 1) 主键约束:primary key 2)非空约束:not null 3)唯一约束:unique 4)外键约束:forei...

       约束:
               对表中的数据(字段)进行限定,保证数据的正确性、有效性和完整性。非法的数据不能够添加到数据库中
            分类: 
                  1) 主键约束:primary key
                  2) 非空约束:not null
                  3) 唯一约束:unique
                  4) 外键约束:foreign key

    1.  主键约束:primary key

           主键是给数据库和程序使用的,不是给最终的客户使用的。主键有没有含义没有关系,只要不重复,非空即可。
          通常不用业务字段作为主键,单独给每张表设计一个 id 的字段,把 id 作为主键,不建议使用学号身份证号等作为主键
            1) 注意:
                       1) 含义:非空且唯一
                       2)一张表只能有一个字段为主键
                       3) 主键就是表中记录的唯一标识

            2) 在创建表时,添加主键约束

    字段名 字段类型 PRIMARY KEY

                           create table stu(
                                   id int primary key,-- 给id添加主键约束
                                   name varchar(20)
                            );

            3) 删除主键
                -- 错误 alter table stu modify id int ;
                ALTER TABLE stu DROP PRIMARY KEY;

            4) 创建完表后,添加主键

    ALTER TABLE 表名 ADD PRIMARY KEY(字段名);  -- 格式1 对字段直接添加主键
    ALTER TABLE stu MODIFY id INT PRIMARY KEY;  -- 格式2 对字段修改成主键

             5) 自动增长(主键):

    AUTO_INCREMENT 表示自动增长(字段类型必须是整数类型)

                        1) 概念:如果某一列是数值类型的,使用 auto_increment 可以来完成值得自动增长

                         2)在创建表时,添加主键约束,并且完成主键自增长
                               create table stu(
                                       id int primary key auto_increment,-- 给id添加主键约束
                                       name varchar(20)
                             );

                         3) 删除自动增长
                                   ALTER TABLE stu MODIFY id INT;
                         4)  添加自动增长
                                   ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

                         5) 默认地 AUTO_INCREMENT 的开始值是 1,如果修改起始值,请使用下列 SQL 语法
                            a) 创建表时指定起始值

    CREATE TABLE 表名(
    列名 int primary key AUTO_INCREMENT
    ) AUTO_INCREMENT=起始值;
    -- 指定起始值为 100
    create table stu (
    id int primary key auto_increment,
    name varchar(20)
    ) auto_increment = 100;
    
    insert into st4 values (null, '张三'); -- 插入数据
                             b) 创建好后修改初始值
    ALTER TABLE 表名 AUTO_INCREMENT=起始值;
    alter table stu auto_increment = 200;
    insert into stu values (null, '李四');
       删除表,两种关键字对主键的影响:
    DELETE 删除所有的记录之后,自增长没有影响。之前自增长到5删除后,继续从6开始
    TRUNCATE 删除以后,自增长又重新开始。从1开始

     

     

     

     

    2. 非空约束:not null

      某一列的值不能为null,限制了一列不能有null值

    字段名 字段类型 NOT NULL

            1) 创建表时添加约束
                create table stu(
                    id int,
                    name varchar(20) not null    -- name为非空
                );
               
            2) 创建表完后,添加非空约束
                ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;

            3) 删除name的非空约束
                ALTER TABLE stu MODIFY NAME VARCHAR(20);    

     

     

    3. 唯一约束:unique

    某一列的值不能重复

    字段名 字段类型 UNIQUE

                     1. 注意:
                             * 唯一约束可以有NULL值,null 没有数据,不存在重复的问题,所以可以插入多个null,不建议
                     2. 在创建表时,添加唯一约束
                             CREATE TABLE stu(
                                  id INT,
                                  phone_number VARCHAR(20) UNIQUE -- 手机号
                               );
                     3. 删除唯一约束
                              ALTER TABLE stu DROP INDEX phone_number;
                     4. 在表创建完后,添加唯一约束
                              ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;
           

     

     

     

     

    4. 外键约束:foreign key

                什么是外键:在从表中与主表主键对应的那一列,如:员工表中的 dep_id
                主表: 一方,用来约束别人的表
                从表: 多方,被别人约束的表

    让表于表产生关系,从而保证数据的正确性。
             1. 在创建表时,可以添加外键
                 语法: [CONSTRAINT] [外键约束名称]可以省略

    [CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)

                           create table 表名(
                                        ....
                                      外键列
                                      constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
                            );

             2. 删除外键
                            ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

             3. 创建表之后,添加外键

    ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主
    键字段名);

       ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);  
             4. 级联操作(联动操作)
         
          以上操作会遇到 更改外键 时,被限制,不能更改和删除操作的问题
                原因是和主表的主键进行了关联,进行了外键限制
                解决方式就是级联操作
               什么是级联操作:
                            在修改和删除主表的主键时,同时更新或删除副表的外键值,称为级联操作
               语法:

    级联操作语法 描述
    ON UPDATE CASCADE 级联更新, 只能是创建表的时候创建级联关系。更新主表中的主键, 从表中的外键列也自动同步更新
    ON DELETE CASCADE 级联删除

                           1. 添加级联操作
                                  语法:ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE  ;
                           2. 分类:
                                     1. 级联更新:ON UPDATE CASCADE 
                                     2. 级联删除:ON DELETE CASCADE 
                  5 .注意点   :
                                    
    外键可以为NULL, 但是不可以为不存在的外键值(和主键一一对应)           
                                     
     

    外键约束综合案例:

    创建一个员工表包含字段(id, name, age, dep_name, dep_location), 其中id 主键并自动增长
    
    CREATE TABLE emp (
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(30),
    age INT,
    dep_name VARCHAR(30),
    dep_location VARCHAR(30)
    );
    
    -- 添加数据
    INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('张三', 20, '研发部', '广州');
    INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('李四', 21, '研发部', '广州');
    INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('王五', 20, '研发部', '广州');
    INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('小明', 20, '销售部', '深圳');
    INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('小王', 22, '销售部', '深圳');
    INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('小张', 18, '销售部', '深圳');

      
    从表中看出总以上数据表的缺点是数据冗余, 后期还会出现增删改的问题
    解决方案:分成 2 张表(此时还未使用外键约束)

    -- 创建部门表(id,dep_name,dep_location)  一方,主表
    
    create table department(
    id int primary key auto_increment,
    dep_name varchar(20),
    dep_location varchar(20)
    );
    
    -- 创建员工表(id,name,age,dep_id) 多方,从表
    
    create table employee(
    id int primary key auto_increment,
    name varchar(20),
    age int,
    dep_id int -- 外键对应主表的主键
    )
    
    -- 添加 2 个部门
    insert into department values(null, '研发部','广州'),(null, '销售部', '深圳');
    
    select * from department;
    
    -- 添加员工,dep_id 表示员工所在的部门
    INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
    INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
    INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
    INSERT INTO employee (NAME, age, dep_id) VALUES ('小明', 20, 2);
    INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 22, 2);
    INSERT INTO employee (NAME, age, dep_id) VALUES ('小张', 18, 2);
    
    select * from employee;
    
    

        
    问题:当我们在 employee 的 dep_id 里面输入不存在的部门,数据依然可以添加.但是并没有对应的部门,
               这时的数据是有误的, SQLyog中有架构器可以查看表设计的关系, 如下两张图 
         

    实际应用中不能出现这种情况
    从表(员工表)employee 的 (外键)dep_id 中的数据只能是(主表)department 表中存在的(主键) id

    问题:  需要约束 dep_id 只能是 department 表中已经存在 id ,
    解决:  使用外键约束

    新建表时增加外键:

    [CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
    已有表增加外键:
    ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主
    键字段名)
    -- 添加外键
    --  删除从表 employee
    
    drop table employee;
    
    -- 创建从表 employee 并添加外键约束 emp_depid_fk
    
    create table employee(
    id int primary key auto_increment,
    name varchar(20),
    age int,
    dep_id int, -- 外键对应主表的主键
    -- 创建外键约束
    constraint emp_depid_fk foreign key (dep_id) references department(id)
    )
    
    -- 正常添加数据:
    INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
    INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
    INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
    INSERT INTO employee (NAME, age, dep_id) VALUES ('小明', 20, 2);
    INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 22, 2);
    INSERT INTO employee (NAME, age, dep_id) VALUES ('小张', 18, 2);

    此时从表就引用主表部门表的主键作为从表的外键使用
                                              

    -- 其他操作
    
    -- 删除 employee 表的 emp_depid_fk 外键
    alter table employee drop foreign key emp_depid_fk;
    
    -- 在 employee 表存在的情况下添加外键
    alter table employee add constraint emp_depid_fk
    foreign key (dep_id) references department(id);


    当再插入受外键约束的数据时报错       
                      

    此时又会有一个问题: 

    select * from department;
    -- 要把部门表中的 id 值 2,改成 5,能不能直接更新呢?
    UPDATE department SET id=5 WHERE id=2;
    
    -- 要删除部门 id 等于 1 的部门, 能不能直接删除呢?
    DELETE FROM department WHERE id=1;
    

     结果:    
             
           遇到以上问题的时候 可以先把从表的外键进行更改成NULL值,然后更改主表的主键, 最后把从表的外键改成和主表主键对应的值。但是遇到细节问题,就是外键可以为NULL, 但是不可以为不存在的外键值(和主键一一对应)
    解决这类问题的办法就是级联操作:   
              添加级联操作格式
               语法:ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE  ;

        第一种方式先删除表的外键, 再进行添加外键的时候进行添加级联更新

    -- 删除外键
    
    ALTER TABLE employee DROP FOREIGN KEY emp_depid_fk;
    
    -- 添加外键 
    alter table employee add constraint emp_deptid_fk foreign key (dep_id)
     references department(id) ;
    
    -- 添加外键 ,设置级联更新
    
    alter table employee add constraint emp_deptid_fk foreign key (dep_id)
     references department(id) on update cascade ;
    
    --  设置级联删除
    alter table employee add constraint emp_deptid_fk foreign key (dep_id)
     references department(id) on delete cascade;
    
    

        第二种方式先删除表, 在重新创建表的时候进行添加级联更新

    第二种方式
    -- 删除 employee 表,重新创建 employee 表,添加级联更新和级联删除
    drop table employee;
    
    create table employee(
    id int primary key auto_increment,
    name varchar(20),
    age int,
    dep_id int, -- 外键对应主表的主键
    -- 创建外键约束
    constraint emp_depid_fk foreign key (dep_id) references
    department(id) on update cascade on delete cascade
    )
    
    
    -- 再次添加数据到员工表和部门表
    INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
    INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
    INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
    INSERT INTO employee (NAME, age, dep_id) VALUES ('小明', 20, 2);
    INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 22, 2);
    INSERT INTO employee (NAME, age, dep_id) VALUES ('小张', 18, 2);
    -- 删除部门表
    drop table department;
    -- 把部门表中 id 等于 1 的部门改成 id 等于 3
    update department set id=10 where id=1;
    
    select * from employee;
    select * from department;
    
    
    -- 删除部门号是 2 的部门
    delete from department where id=2;

    在开发中, 级联的操作一定要慎用, 因为删除一个表的话其他的表或者数据都可能被删除掉

    5. 默认值

      什么是默认值:

    字段名 字段类型 DEFAULT 默认值
    -- 创建一个学生表 stu,包含字段(id,name,address), 地址默认值是北京
    
    create table stu (
    id int,
    name varchar(20),
    address varchar(20) default '北京'
    )
    
    -- 添加一条记录,使用默认地址
    insert into stu values (1, '张三', default); -- 默认北京
    
    insert into stu (id,name) values (2, '李四'); -- 省略default关键字, 默认北京
    
    insert into stu values (3, '王五', '山东');  -- 添加一条记录,不使用默认地址
    
    select * from stu;

     

    如果一个字段设置了非空与唯一约束,该字段与主键的区别?
    1) 主键数在一个表中,只能有一个。不能出现多个主键。主键可以单列,也可以是多列。
    2) 自增长只能用在主键上

    以上约束主要说的是: 

    约束名 关键字 说明
    主键 primary key 1) 唯一
    2) 非空
    非空 not null 这一列必须有值
    唯一 unique 这一列不能有重复值
    外键 foreign key 主表中主键列,在从表中外键列
    默认 default 如果一列没有值,使用默认值

                                                     <约束到此结束>

     

    展开全文
  • 详细分析了 MySQL 支持的各种数据类型,包括数字类型 INETGER、DECIAML、DOUBLE 等,字符串类型 CHAR、VARCHAR、TEXT、BLOB 等,日期时间类型...分析了它们的特点和使用场景,以及如何为表中字段选择合适的数据类型。

    大家好,我是只谈技术不剪发的 Tony 老师。

    在设计数据库时,需要为表中的每个字段指定一个数据类型。数据类型决定了字段中允许存储的数据以及支持的操作,例如字符串允许的最大长度、数字类型可以进行算术运算等;另一方面,不同的类型占用的存储空间和处理性能也不同。因此,本文给大家介绍一下 MySQL 支持的各种数据类型,以及设计表时如何选择合适的字段类型。

    常见数据类型

    MySQL 实现了 SQL 标准中定义的大部分数据类型,主要可以分为以下几类:数字类型、字符串类型、日期和时间类型、JSON 数据类型以及空间类型。

    datatype

    数字类型

    MySQL 实现了 SQL 标准中的精确数字类型和近似数字类型,包括整数(INTEGER、SMALLINT)、定点数(DECIMAL、NUMERIC)和浮点数(FLOAT、REAL、DOUBLE PRECISION)。这些数字类型又可以分为有符号类型和无符号类型。

    📝关于数字类型的算术运算和数值处理的函数,可以参考这篇文章

    整数数字

    下表列出了 MySQL 中的所有整数类型:

    数字类型 存储(字节) 有符号类型最小值 有符号类型最大值 无符号类型最小值 无符号类型最大值
    TINYINT 1 -128 127 0 255
    SMALLINT 2 -32768 32767 0 65535
    MEDIUMINT 3 -8388608 8388607 0 16777215
    INTEGER 4 -2147483648 2147483647 0 4294967295
    BIGINT 8 263 263-1 0 264-1

    整数类型的定义如下:

    TINYINT[(M)] [UNSIGNED] [ZEROFILL]
    SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
    MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
    INT[(M)] [UNSIGNED] [ZEROFILL]
    INTEGER[(M)] [UNSIGNED] [ZEROFILL]
    BIGINT[(M)] [UNSIGNED] [ZEROFILL]
    

    其中,INT 是 INTEGER 的同义词。M 表示的是显示宽度,不会影响数据的存储;如果实际数据小于指定宽度,可以指定 ZEROFILL 在左侧使用 0 填充显示。UNSIGNED 表示无符号整数;如果指定了 ZEROFILL,MySQL 会自动加上 UNSIGNED。例如:

    DROP TABLE IF EXISTS t;
    CREATE TABLE t(id int(4) ZEROFILL);
    INSERT INTO t VALUES (1), (99999);
    
    mysql> SELECT * FROM t;
    +-------+
    | id    |
    +-------+
    |  0001 |
    | 99999 |
    +-------+
    2 rows in set (0.00 sec)
    
    mysql> desc t;
    +-------+--------------------------+------+-----+---------+-------+
    | Field | Type                     | Null | Key | Default | Extra |
    +-------+--------------------------+------+-----+---------+-------+
    | id    | int(4) unsigned zerofill | YES  |     | NULL    |       |
    +-------+--------------------------+------+-----+---------+-------+
    1 row in set (0.00 sec)
    

    ⚠️从 MySQL 8.0.17 开始,整数类型的显示宽度 M 和 ZEROFILL 选项已经被弃用,将来的版本中会删除。可以考虑使用其他方法实现相同的效果,例如使用 LPAD() 函数将数字填充至指定宽度,或者使用 CHAR 类型存储格式化的数字。

    对于 BIGINT 类型,需要注意以下内容:

    • SERIAL 是 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE 的同义词;
    • 所有的算术运算都使用有符号的 BIGINT 或者 DOUBLE 数据进行运算,所以不要使用大于 9223372036854775807 的无符号大整数参与运算,除非使用位函数和运算符。如果参与了运算,由于将 BIGINT 转换为 DOUBLE 时存在舍入错误,可能会导致结果中的最后几位数字出错。MySQL 会在以下情况中使用 BIGINT:使用整数类型存储 BIGINT 字段中的无符号大整数;针对 BIGINT 字段的 MIN() 或者 MAX() 函数;两个整数的算术运算(+、-、* 等);
    • 可以将 BIGINT 数据存储为字符串, MySQL 在运算时会执行字符串到数字的类型转换,而不会涉及到双精度格式的中间结果。
    • 当两个操作数都是整数类型时,-、+ 和 * 运算符使用 BIGINT 算术。这意味着如果将两个大整数(或者返回整数值的函数)相乘,如果结果大于 9223372036854775807,可能会得到一个异常的结果。

    定点数字

    MySQL 使用 DECIMAL 和 NUMERIC 类型存储精确的数字值,通常用于需要保留完整精确的字段,例如财务系统中的货币余额。MySQL 中的 NUMERIC 和 DECIMAL 时同义词。

    DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]
    DEC[(M[,D])] [UNSIGNED] [ZEROFILL]
    NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL]
    FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]
    

    其中,DEC 和 FIXED 是 DECIMAL 的同义词。M 表示可以存储的总位数(精度),最大取值为 65,默认值为 10,小数点和负号(-)不算位数;D 是小数点后的位数(刻度),最大取值为 30 并且小于等于 M,默认值为 0(表示整数)。ZEROFILL 表示如果实际数据小于指定宽度,显示时在左侧使用 0 填充。UNSIGNED 表示无符号数字;如果指定了 ZEROFILL,MySQL 会自动加上 UNSIGNED。例如:

    DROP TABLE IF EXISTS t;
    CREATE TABLE t(salary DECIMAL(5,2));
    INSERT INTO t VALUES (-999.99), (0), (999.99);
    
    mysql> INSERT INTO t VALUES (1000);
    ERROR 1264 (22003): Out of range value for column 'salary' at row 1
    
    mysql> INSERT INTO t VALUES (0.001);
    Query OK, 1 row affected, 1 warning (0.01 sec)
    
    mysql> show warnings;
    +-------+------+---------------------------------------------+
    | Level | Code | Message                                     |
    +-------+------+---------------------------------------------+
    | Note  | 1265 | Data truncated for column 'salary' at row 1 |
    +-------+------+---------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> SELECT * FROM t;
    +---------+
    | salary  |
    +---------+
    | -999.99 |
    |    0.00 |
    |  999.99 |
    |    0.00 |
    +---------+
    4 rows in set (0.00 sec)
    

    其中,salary 字段的精度为 5,刻度为 2。因此,该字段可以存储 -999.99 到 -999.99 之间的数值。此时,插入超过精度的数据返回错误,插入超过刻度的数据会进行四舍五入。

    MySQL 中的 DECIMAL 和 DECIMAL(M) 等价于 DECIMAL(M,0),M 的默认值为 10。

    ⚠️从 MySQL 8.0.17 开始,DECIMAL 类型的 UNSIGNED 选项已经被弃用,将来的版本中会删除。可以考虑使用 CHECK 约束实现相同的功能。

    DECIMAL 数据使用二进制格式进行存储,9 位十进制数字存储为 4 个字节,整数部分和小数部分的数值分别存储。每 9 位数字需要 4 字节存储,剩余的位数需要的存储空间如下表所示:

    剩余位数 字节数
    0 0
    1–2 1
    3–4 2
    5–6 3
    7–9 4

    例如,DECIMAL(18,9) 在小数点两边各有 9 位,所以整数部分和小数部分各需要 4 字节存储;DECIMAL(20,6) 拥有 14 位整数和 6 位小数,整数部分需要 4 字节(9 位数字)加上 3 字节(7 位数字),6 位小数部分需要 3 字节存储。

    DECIMAL 字段不会存储前置的 +、- 字符或者 0。如果为 DECIMAL(5,1) 字段插入 +0003.1,最终会存储 3.1。对于负数,不会存储负号。

    浮点数字

    MySQL 使用 FLOAT 和 DOUBLE 类型表示近似数字,对于单精度使用 4 字节存储,双精度使用 8 字节存储。

    FLOAT(p) [UNSIGNED] [ZEROFILL]
    

    其中,p 是以比特为单位的精度。MySQL 中的 p 只用于决定最终类型为 FLOAT 还是 DOUBLE,如果 p 的值为 0 到 24 则为 FLOAT,如果 p 的值为 25 到 53 则为 DOUBLE;ZEROFILL 表示如果实际数据小于指定宽度,显示时在左侧使用 0 填充。UNSIGNED 表示无符号数字;如果指定了 ZEROFILL,MySQL 会自动加上 UNSIGNED。

    对于单精度 FLOAT 类型,理论上支持 -3.402823466E+38 到 -1.175494351E-38、0、以及 1.175494351E-38 到 3.402823466E+38 之间的数值,实际上取决于硬件和操作系统。

    对于双精度 DOUBLE 类型,理论上支持 -1.7976931348623157E+308 到 -2.2250738585072014E-308、0 以及 2.2250738585072014E-308 到 1.7976931348623157E+308 之间的数值,实际上取决于硬件和操作系统。

    📝DOUBLE 是 DOUBLE PRECISION 的同义词。如果指定了 SQL 模式 REAL_AS_FLOAT,REAL 是 FLOAT 的同义词;否则它是 DOUBLE PRECISION 的同义词。

    由于浮点类型存储的是近似数值而不是精确数值,如果尝试将它们进行比较可能会返回错误的结果,而且结果和平台或者具体实现相关。例如:

    mysql> SELECT (1.0/3)*3, (1.0/3)*3=1.0;
    +-----------+---------------+
    | (1.0/3)*3 | (1.0/3)*3=1.0 |
    +-----------+---------------+
    |   1.00000 |             0 |
    +-----------+---------------+
    1 row in set (0.00 sec)
    

    虽然 (1.0/3)*3 的结果显示为 1.00000,但是在系统内部它并不等于 1.0。

    📝为了方便移植,存储近似数值的数据类型应该使用 FLOAT 或者 DOUBLE PRECISION 定义,而不需要指定精度或者位数。

    除了以上定义之外,MySQL 还提供了一些非标准的语法:

    FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
    DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
    DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL]
    REAL[(M,D)] [UNSIGNED] [ZEROFILL]
    
    

    其中,M 表示可以存储的总位数(精度),D 是小数点后的位数(刻度);如果省略 M 和 D,具体的范围取决于硬件实现,单精度浮点数大概支持 7 位小数,双精度浮点数大概支持 15 位小数。例如,FLOAT(7,4) 类型显示时的内容可能为 -999.9999。MySQL 存储数据值会进行舍入,如果插入 999.00009,近似的结果为 999.0001。

    ⚠️从 MySQL 8.0.17 开始,FLOAT、DOUBLE 类型的 UNSIGNED 属性已经被弃用,将来的版本中会删除。可以考虑使用 CHECK 约束实现相同的功能。从 MySQL 8.0.17 开始,非标准的 FLOAT(M,D) 和 DOUBLE(M,D) s语法已经被弃用,将来的版本中会删除。

    BIT 类型

    MySQL 中的 InnoDB、MyISAM、MEMORY 以及 NDB 存储引擎支持特殊的位类型(BIT),用于存储比特数据值。

    BIT(M)
    

    其中,M 表示比特个数,范围从 1 到 64,默认为 1;BIT(M) 大概需要 (M+7)/8 字节的存储。例如:

    CREATE TABLE work_days(
        year INT,
        week INT,
        days BIT(7),
        PRIMARY KEY(year, week)
    );
    INSERT INTO work_days
    VALUES(2020, 1, b'0110110');
    

    其中,days 表示这一周中的每一天是否是工作日,1 表示工作日,0 表示周末或者假期。INSERT 语句表示 2020 年第一周的周日、周三和周六是休息日。

    位类型的常量可以使用以下格式指定:

    b'111'
    B'111'
    0b111
    

    以上数据表示十进制中的 3。查询时可以使用 BIN 函数进行转换显示:

    mysql> select year, week, bin(days) from work_days;
    +------+------+-----------+
    | year | week | bin(days) |
    +------+------+-----------+
    | 2020 |    1 | 110110    |
    +------+------+-----------+
    1 row in set (0.00 sec)
    

    布尔类型

    MySQL 没有提供内置的 BOOLEAN 或者 BOOL 数据类型,而是使用 TINYINT(1) 进行表示。以下三种语法等价:

    BOOL
    BOOLEAN
    TINYINT(1)
    

    在 MySQL 中,0 被看作 false,非 0 的数值被看作 true。布尔类型的常量可以使用 TRUE 和 FALSE 表示,结果分别为 1 和 0。

    mysql> SELECT IF(0, 'true', 'false');
    +------------------------+
    | IF(0, 'true', 'false') |
    +------------------------+
    | false                  |
    +------------------------+
    
    mysql> SELECT IF(1, 'true', 'false');
    +------------------------+
    | IF(1, 'true', 'false') |
    +------------------------+
    | true                   |
    +------------------------+
    
    mysql> SELECT IF(2, 'true', 'false');
    +------------------------+
    | IF(2, 'true', 'false') |
    +------------------------+
    | true                   |
    +------------------------+
    

    不过, TRUE 和 FALSE 仅仅是 1 和 0 的别名。例如:

    mysql> SELECT IF(0 = FALSE, 'true', 'false');
    +--------------------------------+
    | IF(0 = FALSE, 'true', 'false') |
    +--------------------------------+
    | true                           |
    +--------------------------------+
    
    mysql> SELECT IF(1 = TRUE, 'true', 'false');
    +-------------------------------+
    | IF(1 = TRUE, 'true', 'false') |
    +-------------------------------+
    | true                          |
    +-------------------------------+
    
    mysql> SELECT IF(2 = TRUE, 'true', 'false');
    +-------------------------------+
    | IF(2 = TRUE, 'true', 'false') |
    +-------------------------------+
    | false                         |
    +-------------------------------+
    
    mysql> SELECT IF(2 = FALSE, 'true', 'false');
    +--------------------------------+
    | IF(2 = FALSE, 'true', 'false') |
    +--------------------------------+
    | false                          |
    +--------------------------------+
    

    最后两个语句都返回了 false,因为 2 即不等于 1,也不等于 0。

    字符串类型

    MySQL 字符串类型用于存储字符和字符串数据,包括二进制数据,例如图片或者文件。字符串数据可以支持比较运算符和模式匹配运算符,例如 LIKE、正则表达式匹配以及全文检索。

    MySQL 支持的字符串类型包括 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 以及 SET,其中 CHAR、VARCHAR、TEXT、ENUM 以及 SET 包含字符集(Charset)和排序规则(Collation)属性,默认继承表的字符集和排序规则。MySQL 8.0 默认使用 utf8mb4 字符集。

    📝关于字符串处理和分析的函数和操作符,可以参考这篇文章

    CHAR

    CHAR(n) 和 CHARACTER(n) 类型表示长度固定的字符串,其中 n 表示字符串中字符的最大数量,取值范围从 0 到 255。例如:

    CREATE TABLE t (c1 CHAR, c2 CHAR(5));
    INSERT INTO t VALUES ('a','a');
    

    其中,c1 只能存储 1 个字符;c2 最多能够存储 5 个字符。对于定长字符串,如果输入的字符串长度不够,将会使用空格进行填充。因此,字段 c2 中实际存储的内容为“a”加上 4 个空格。

    mysql> select concat(c1, '!'), concat(c2, '!') from t;
    +-----------------+-----------------+
    | concat(c1, '!') | concat(c2, '!') |
    +-----------------+-----------------+
    | a!              | a!              |
    +-----------------+-----------------+
    1 row in set (0.00 sec)
    
    mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql> select concat(c1, '!'), concat(c2, '!') from t;
    +-----------------+-----------------+
    | concat(c1, '!') | concat(c2, '!') |
    +-----------------+-----------------+
    | a!              | a    !          |
    +-----------------+-----------------+
    1 row in set (0.00 sec)
    
    mysql> SET sql_mode = default;
    Query OK, 0 rows affected (0.00 sec)
    

    默认情况下没有设置 SQL 模式 PAD_CHAR_TO_FULL_LENGTH,MySQL 读取 CHAR 字段时自动截断了尾部的空格。

    MySQL 使用比较运算符(=、<>、>、< 等)和 LIKE 操作符比较和匹配 CHAR 字段数据时不考虑尾部的空格。例如:

    mysql> select c2 from t where c2='a';
    +------+
    | c2   |
    +------+
    | a    |
    +------+
    1 row in set (0.00 sec)
    
    mysql> select c2 from t where c2 like 'a    ';
    Empty set (0.00 sec)
    

    📝通常来说,只有存储固定长度的数据时,才会考虑使用定长字符串类型。例如 18 位身份证,6 位邮政编码等。

    VARCHAR

    VARCHAR(n) 和 CHARACTER VARYING(n) 类型表示长度不固定的字符串,其中 n 表示字符串中字符的最大数量,取值范围从 0 到 65535。例如:

    DROP TABLE IF EXISTS t;
    CREATE TABLE t (c VARCHAR(5));
    INSERT INTO t VALUES ('a    '), ('abcde');
    

    字段 c 最多存储 5 个字符。

    📝VARCHAR 字段的实际最大长度受限于最大的行大小(65536 字节,所有字段长度之和)以及字符集。例如,utf8mb4 字符集中的一个字符最多占用 4 个字节,因此这种字符集的 VARCHAR 字段可以声明的最大长度为 16383。

    对于变长字符串,如果输入的字符串长度不够,存储实际的内容。例如类型为 VARCHAR(5) 的字段,如果输入值为“a”,实际存储的内容为“a”。SQL 模式 PAD_CHAR_TO_FULL_LENGTH 对 VARCHAR 字段没有影响,MySQL 读取 VARCHAR 字段时不会截断尾部的空格。

    mysql> select c,length(c) from t;
    +-------+-----------+
    | c     | length(c) |
    +-------+-----------+
    | a     |         5 |
    | abcde |         5 |
    +-------+-----------+
    2 rows in set (0.00 sec)
    

    MySQL 存储的 VARCHAR 数据包括 1 字节或 2 字节的长度信息前缀加上具体数据,长度前缀标识了数据的字节数。如果 VARCHAR 字段的数据小于等于 255 字节,使用 1 个字节存储长度;如果数据可能大于等于 256 字节,使用 2 个字节存储长度。

    📝通常来说,变长字符串类型一般用于存储长度不固定的内容,例如名字、电子邮箱、产品描述等。

    TEXT

    MySQL 提供了 4 种形式的 TEXT 类型:TINYTEXT、TEXT、MEDIUMTEXT 以及 LONGTEXT。

    • TINYTEXT,最大长度为 255 个字节,类似于 VARCHAR(255)。每个 TINYTEXT 值需要 1 字节额外的存储表示长度;
    • TEXT,最大长度为 65535 个字节,类似于 VARCHAR(65535)。每个 TEXT 值需要 2 字节额外的存储表示长度;
    • MEDIUMTEXT,最大长度为 16777215 个字节。每个 MEDIUMTEXT 值需要 3 字节额外的存储表示长度;
    • LONGTEXT,最大长度为 4294967295 个字节。每个 LONGTEXT 值需要 4 字节额外的存储表示长度。

    TEXT 类型可以用于存储长文本字符串,长度支持 1 字节到 4 GB;但是 MySQL 不会在服务器内存中缓存 TEXT 数据,而是从磁盘中读取,所有访问时比 CHAR 和 VARCHAR 类型更慢一些。MySQL 插入或者查询时不会对 TEXT 数据尾部空格进行任何处理。

    📝TEXT 数据类型通常用于存储文章内容、产品描述等信息。

    二进制字符串

    BINARY(M) 和 VARBINARY(M) 类型与 CHAR 和 VARCHAR 类型类似,但是存储的内容为二进制字节串,而不是普通字符串。其中 M 表示最大的字节长度,分别为 255 和 65535。这两种类型使用 binary 字符集和排序规则,基于字节数值进行比较和排序。

    存储 BINARY 数据时,在尾部使用 0x00(字节 0)填充到指定长度,查询时不会删除尾部的 0 字节。所有的字节对应比较操作都有意义,包括 ORDER BY 和 DISTINCT 操作,0x00 和空格比较的结果不相等,0x00 的排序在空格前面。

    DROP TABLE IF EXISTS t;
    CREATE TABLE t (c BINARY(3));
    INSERT INTO t VALUES ('a');
    
    mysql> SELECT * FROM t where c='a';
    Empty set (0.00 sec)
    
    mysql> SELECT * FROM t where c='a\0\0';
    +------------+
    | c          |
    +------------+
    | 0x610000   |
    +------------+
    1 row in set (0.00 sec)
    

    存储 VARBINARY 数据时,不会使用 0x00(字节 0)填充,查询时不会删除尾部的 0 字节。所有的字节对应比较操作都有意义,包括 ORDER BY 和 DISTINCT 操作,0x00 和空格比较的结果不相等,0x00 的排序在空格前面。

    BINARY(M) 和 VARBINARY(M) 类型与 CHAR 和 VARCHAR 类型的存储需求也类似,但是以字节为单位。

    BLOB

    MySQL 提供了 4 种形式的 BLOB 类型:TINYBLOB、BLOB、MEDIUMBLOB 以及 LONGBLOB。

    • TINYBLOB,最大长度为 255 个字节,类似于 VARBINARY(255)。每个 TINYBLOB 值需要 1 字节额外的存储表示长度;
    • BLOB,最大长度为 65535 个字节,类似于 VARBINARY(65535)。每个 BLOB 值需要 2 字节额外的存储表示长度;
    • MEDIUMBLOB,最大长度为 16777215 个字节。每个 MEDIUMBLOB 值需要 3 字节额外的存储表示长度;
    • LONGBLOB,最大长度为 4294967295 个字节。每个 LONGBLOB 值需要 4 字节额外的存储表示长度。

    BLOB 类型可以用于存储二进制大对象,长度支持 1 字节到 4 GB;BLOB 类型使用 binary 字符集和排序规则,基于字节数值进行比较和排序。MySQL 不会在服务器内存中缓存 BLOB 数据,而是从磁盘中读取,所有访问时比 BINARY 和 VARBINARY 类型更慢一些。MySQL 插入或者查询时不会对 BLOB 数据尾部空格进行任何处理。

    📝BLOB 数据类型通常用于存储图片、文档、视频等信息。

    ENUM

    ENUM(‘value1’,‘value2’,…) 类型定义了一个枚举,即取值限定为 ‘value1’、‘value2’、…、NULL 或者 ‘’ 之一的字符串对象。ENUM 数据在内部使用整数表示,最多包含 65535 个不同的值。

    每个枚举元素最大的长度为 M <= 255 并且 (M x w) <= 1020,其中 M 是元素的字面长度,w 是字符集中字符可能占用的最大字节数。

    使用枚举类型的优势在于:

    • 在字段的取值有限时提供紧凑的数据存储,枚举在内部使用整数表示,需要 1 字节或 2 字节存储;
    • 查询结果的可读性,内部整数在 查询结果中显示为相应的字符串。

    例如:

    CREATE TABLE shirts (
        name VARCHAR(40),
        size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
    );
    INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'), ('polo shirt','small');
    
    SELECT name, size FROM shirts WHERE size = 'medium';
    +---------+--------+
    | name    | size   |
    +---------+--------+
    | t-shirt | medium |
    +---------+--------+
    

    如果插入 100 万条 ‘medium’ 数据,需要 100 万字节存储;如果直接使用 VARCHAR 类型,需要 6 倍存储。

    另一方面,使用枚举类型时需要注意枚举值的排序使用内部的索引数字,而不是字符串。例如,对于 ENUM(‘b’, ‘a’) 字符 b 排在 a 之前。

    SET

    SET(‘value1’,‘value2’,…) 类型定义了一个集合,即取值限定为 ‘value1’、‘value2’、…中零个或多个的字符串对象。SET 数据在内部使用整数表示,最多包含 64 个不同的成员。

    每个集合元素最大的长度为 M <= 255 并且 (M x w) <= 1020,其中 M 是元素的字面长度,w 是字符集中字符可能占用的最大字节数。

    例如:

    DROP TABLE IF EXISTS t;
    CREATE TABLE t (c SET('a','b'));
    INSERT INTO t VALUES (''),('a'),('b'),('a,b');
    
    mysql> SELECT c+0 FROM t;
    +------+
    | c+0  |
    +------+
    |    0 |
    |    1 |
    |    2 |
    |    3 |
    +------+
    4 rows in set (0.00 sec)
    
    

    SET 对象的存储空间由集合成员的个数决定;如果个数为 N,对象占用 (N+7)/8 字节,向上取整为 1、2、3、4 或者 8 字节。

    日期时间类型

    MySQL 提供了以下存储时间值的数据类型:DATE、TIME、DATETIME、TIMESTAMP 以及 YEAR。其中,TIME、DATETIME、TIMESTAMP 支持小数秒,最多 6 位小数(微秒)。

    📝关于日期时间数据处理的函数和操作符,可以参考这篇文章

    日期类型

    DATE 表示日期类型,支持的范围从 ‘1000-01-01’ 到 ‘9999-12-31’,占用 3 个字节。DATE 数据的显示格式为 ‘YYYY-MM-DD’。例如:

    DROP TABLE IF EXISTS t;
    CREATE TABLE t (birth_date date);
    INSERT INTO t VALUES ('2020-10-01');
    
    mysql> SELECT * FROM t;
    +------------+
    | birth_date |
    +------------+
    | 2020-10-01 |
    +------------+
    1 row in set (0.00 sec)
    

    MySQL 使用 4 位数字存储日期数据中的年份,如果输入 2 位年份,将会使用以下规则:

    • 00-69 之间的年份转换为 2000-2069;
    • 70-99 之间的年份转换为 1970 – 1999。

    例如:

    INSERT INTO t VALUES ('01-10-31'), ('81-10-31');
    
    mysql> SELECT * FROM t;
    +------------+
    | birth_date |
    +------------+
    | 2020-10-01 |
    | 2001-10-31 |
    | 1981-10-31 |
    +------------+
    3 rows in set (0.00 sec)
    

    以上规则同样适用于其他数据类型中的年份信息,包括 DATETIME、TIMESTAMP 以及 YEAR。

    时间类型

    MySQL 使用 TIME 类型表示一天中的时间,格式为 ‘HH:MM:SS’,范围小于 24 小时。另外,也可以使用 TIME 表示两个事件之间的时间间隔,格式为 ‘hhh:mm:ss’,范围从 ‘-838:59:59’ 到 ‘838:59:59’。TIME 类型需要 3 字节的存储。例如:

    DROP TABLE IF EXISTS t;
    CREATE TABLE t (start_time time, end_time time);
    INSERT INTO t VALUES ('09:00:00', '10:00:00');
    
    mysql> SELECT * FROM t;
    +------------+----------+
    | start_time | end_time |
    +------------+----------+
    | 09:00:00   | 10:00:00 |
    +------------+----------+
    1 row in set (0.00 sec)
    

    MySQL 使用 TIME(N) 表示包含小数部分的时间,最多包含 6 位小数(微秒),默认为 0 位。如果包含了小数秒,TIME 需要额外的存储,TIME(1) 和 TIME(2) 需要 4 字节,TIME(3) 和 TIME(3) 需要 5 字节,TIME(5) 和 TIME(6) 需要 6 字节存储。

    时间戳类型

    DATETIME(N) 和 TIMESTAMP(N) 类型可以同时存储日期(DATE)和时间(TIME)信息,也就是时间戳。

    DATETIME 类型使用 ‘YYYY-MM-DD hh:mm:ss[.fraction]’ 格式显示,支持范围 ‘1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’,默认 0 位小数秒,需要 5 字节存储。如果支持小数秒,额外的存储和 TIME(N) 类似。

    TIMESTAMP 类型使用 UTC 时区进行存储,支持范围 ‘1970-01-01 00:00:01.000000’ UTC 到 ‘2038-01-19 03:14:07.999999’ UTC,默认 0 位小数秒,需要 4 字节存储。如果支持小数秒,额外的存储和 TIME(N) 类似。

    例如:

    DROP TABLE IF EXISTS t;
    CREATE TABLE t (dt DATETIME, ts TIMESTAMP);
    
    SET time_zone = '+00:00';
    INSERT INTO t VALUES (now(), now());
    
    mysql> SELECT * FROM t;
    +---------------------+---------------------+
    | dt                  | ts                  |
    +---------------------+---------------------+
    | 2020-09-10 14:09:57 | 2020-09-10 14:09:57 |
    +---------------------+---------------------+
    1 row in set (0.00 sec)
    

    两者在 UTC 时区相同,然后修改会话的时区:

    mysql> SET time_zone = '+08:00';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SELECT * FROM t;
    +---------------------+---------------------+
    | dt                  | ts                  |
    +---------------------+---------------------+
    | 2020-09-10 14:09:57 | 2020-09-10 22:09:57 |
    +---------------------+---------------------+
    1 row in set (0.00 sec)
    

    结果显示,TIMESTAMP 类型会随着当前时区进行调整。

    DATETIME 和 TIMESTAMP 类型支持自动初始化或者更新为当前日期时间,在字段定义时分别使用 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 属性进行设置。例如:

    DROP TABLE IF EXISTS t;
    CREATE TABLE t (
      id int, 
      dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    );
    INSERT INTO t(id) VALUES (1);
    
    mysql> SELECT * FROM t;
    +------+---------------------+---------------------+
    | id   | dt                  | ts                  |
    +------+---------------------+---------------------+
    |    1 | 2020-09-10 14:33:25 | 2020-09-10 22:33:25 |
    +------+---------------------+---------------------+
    1 row in set (0.00 sec)
    

    YEAR

    如果只需要存储年份信息,可以使用 YEAR 类型。YEAR 类型占用 1 个字节,显示格式位 ‘YYYY’,范围从 1901 到 2155,以及 0000。

    JSON 数据类型

    MySQL 5.7.8 开始支持原生 JSON 数据类型,可以支持更加高效的 JSON 文档存储和管理。原生 JSON 数据类型提供了自动的格式验证以及优化的存储格式,可以快速访问文档中的元素节点。例如:

    CREATE TABLE employee_json(
      emp_id    INTEGER NOT NULL PRIMARY KEY,
      emp_info  JSON NOT NULL
    );
    INSERT INTO employee_json 
    VALUES (1, '{"emp_name": "刘备", "sex": "男", "dept_id": 1, "manager": null, "hire_date": "2000-01-01", "job_id": 1, "income": [{"salary":30000}, {"bonus": 10000}], "email": "liubei@shuguo.com"}');
    
    mysql> INSERT INTO employee_json VALUES (2,'{"emp_name":  "刘备" ');
    ERROR 3140 (22032): Invalid JSON text: "Missing a comma or '}' after an object member." at position 23 in value for column 'employee_json.emp_info'.
    

    除了 JSON 数据类型之外,MySQL 还提供了许多 JSON 处理函数和操作符,例如构造 JSON 对象的 JSON_OBJECT、JSON_ARRAY,查询指定元素的 ->(JSON_EXTRACT)、->> (JSON_UNQUOTE + JSON_EXTRACT),将 JSON 数据转换为 SQL 数据的 JSON_TABLE、更新 JSON 数据的 JSON_SET、JSON_INSERT、JSON_REPLACE、JSON_REMOVE 以及格式验证的 JSON_VALID 函数。例如:

    SELECT emp_id, jt.*
      FROM employee_json,
           JSON_TABLE(emp_info, '$'
             COLUMNS (emp_name  VARCHAR(50) PATH '$.emp_name',
                      sex       VARCHAR(10) PATH '$.sex',
                      dept_id   INTEGER PATH '$.dept_id',
                      manager   INTEGER PATH '$.manager',
                      hire_date DATE PATH '$.hire_date',
                      job_id    INTEGER PATH '$.job_id',
                      salary    INTEGER PATH '$.income[0].salary',
                      bonus     INTEGER PATH '$.income[1].bonus',
                      email     VARCHAR(100) PATH '$.email')
           ) jt;
    emp_id|emp_name|sex|dept_id|manager|hire_date |job_id|salary|bonus|email                   |
    ------|--------|---|-------|-------|----------|------|------|-----|------------------------|
         1|刘备     ||      1|       |2000-01-01|     1| 30000|10000|liubei@shuguo.com       |
    

    一般来说,JSON 字段所需的存储和 LONGBLOB 或者 LONGTEXT 差不多。不过,JSON 文档的二进制编码需要额外的存储,包括元数据和字典信息。举例来说,JSON 文档中的字符串需要额外的 4 到 10 个字节存储。除此之外,JSON 文档的最大长度不能超过系统变量 max_allowed_packet 的限制。

    📝关于 MySQL 中的文档存储功能,可以参考这篇文章

    空间数据类型

    MySQL 支持许多包含各种几何和地理数据的空间数据类型,包括:

    • GEOMETRY,任何类型的空间数据值;
    • POINT,X-Y 坐标系中的点;
    • LINESTRING,曲线,一个或多个 POINT 数据值;
    • POLYGON,多边形;
    • GEOMETRYCOLLECTION,GEOMETRY 数据集合;
    • MULTILINESTRING,LINESTRING 数据集合;
    • MULTIPOINT,POINT 数据集合;
    • MULTIPOLYGON,POLYGON 数据集合。

    MySQL 存储空间数据时使用 4 字节标识 SRID(空间参照标识符),然后存储 Well-Known Binary 格式的数据。LENGTH() 函数可以返回数据占用的字节数。

    MySQL 中的 MyISAM、InnoDB、NDB 以及 ARCHIVE 存储引擎支持空间数据类型的存储和处理函数,MyISAM 和 InnoDB 存储引擎支持空间字段的 SPATIAL 索引。

    关于 MySQL 空间数据扩展,具体参考官方文档

    选择合适的数据类型

    最后我们来看看如何选择合适的数据类型。首先,应该满足存储业务数据的需求;其次,还需要考虑性能和使用方便。一般来说,先确定基本的类型:

    • 文本数据,使用字符串类型;
    • 数值数据,尤其是需要进行数学运算的数据,选择数字类型;
    • 日期和时间信息,最好使用原生的日期时间类型,也可以考虑数字类型;
    • 文档、图片、音频和视频等,使用二进制类型;或者可以考虑存储在文件服务器上,然后在数据库中存储文件的路径。

    接下来需要进一步确定具体的数据类型。在满足数据存储和扩展的前提下,尽量使用更小的数据类型,可以节省一些存储,通常性能也会更好。例如,对于一个小型公司而言,员工编号通常不会超过几百,使用 SMALLINT 已经足够。对于 MySQL 而言,不需要支持负数的话可以考虑 UNSIGNED 类型。

    对于字符数据,一般使用 VARCHAR 类型;如果数据长度能够确保一致,可以使用 CHAR;指定最大长度时,满足存储需求的前提下尽量使用更小的值。只有在普通字符串类型长度无法满足时才考虑 TEXT 字段类型。

    如果需要存储精确的数字,不要使用浮点数类型。对于金额,可以使用 DECIMAL(p, s);或者将数据乘以 10 的 N 次方,例如将 10.35 元存储为整数 103500,然后在应用程序中进行处理和前端显示转换。

    只需要年月日信息时使用 DATE 类型,例如出生日期;只需要时间信息时使用 TIME 类型,例如发车时间;选择时间戳类型时需要注意 DATETIME 和 TIMESTAMP 的区别。

    可以考虑将日期时间拆成多个数字类型存储,或者使用 UNIX 时间戳表示;但是不要使用字符串存储日期时间数据,它们无法支持数据的运算。例如获得两个日期之间的间隔,需要依赖应用程序进行转换和处理。最好也不要使用整数类型存储当前时间距离 1970 年 1 月 1 日的毫秒数来表示时间,这种方式在显示时需要进行转换,不是很方便。

    对于特殊应用场景,考虑使用其他类型。例如电商的产品信息可以考虑使用 JSON 数据类型,处理地理位置信息使用空间数据类型等。

    另外,如果一个字段同时出现在多个表中,使用相同的数据类型。例如,员工表中的部门编号(dept_id)字段与部门表的编号(dept_id)字段保持类型一致。

    总结

    本文详细分析了 MySQL 中的各种数据类型以及选择数据类型时的一些通用的原则,使用任何数据类型之前都应该查看相关的数据库文档。

    如果觉得文章对你有用,欢迎关注❤️、评论📝、点赞👍!

    展开全文
  • 如要在一个hwsp表中填加字段:ylxsalter table hwsp add column ylxsALTER TABLE {ADD ADD{COLUMN 字段类型 [ (字长)] [NOT NULL] [CONSTRAINT 索引 ] | ALTER COLUMN 字段类型 [(字长)] | CONSTRAINT 多重...

     

    如要在一个hwsp表中填加字段:ylxs

    alter table hwsp add column ylxs


    ALTER TABLE 表 {ADD ADD{COLUMN 字段类型 [ (字长)] [NOT NULL]     [CONSTRAINT 索引 ] |
         ALTER COLUMN 字段类型 [(字长)] |
         CONSTRAINT 多重字段索引 } |
         DROP DROP{COLUMN 字段 I CONSTRAINT 索引名 } }

    ALTER TABLE 语句分为以下几个部分:

    部分 说明
    table 欲修改的表之名称。
    field 欲在表内增加或删除的字段的名称。或表内欲被替换的字段名称。
    type 字段的数据类型。
    size 字段的字符长度 (文本及二进制字段)。
    index 字段索引。欲了解更多有关如何构造本索引的信息,请看 CONSTRAINT子句。  
    multifieldindex 欲增加至表 中的多重字段索引的定义。欲了解更多有关如何构造本索引的信息,请看 CONSTRAINT子句。
    indexname 欲删除的多重字段索引的名称。


    说明
    使用 ALTER TABLE 语句,可用多种不同方法更改当前已存在的表:你可以:

    使用 ADD COLUMN 在表中添加新的字段。需要指定字段名、数据类型、还可以 (对文本和二进制字段)指定长度。例如,下列语句在员工表中增加一 25 个字符的、名为 Notes 的文本字段:
    ALTER TABLE Employees ADD COLUMN Notes TEXT(25)


    也可以定义此字段的索引。关于单一字段索引的详细信息,请参阅CONSTRAINT 子句主题。

    如果对一字段指定 NOT NULL,则在这字段中添加的新记录必须有有效的数据。

    使用ALTER COLUMN改变一个当前字段的数据类型,需要指定字段名、新数据类型、还可以 (对文本和二进制字段)指定长度。例如,下列语句把雇员表中一个字段的数据类型, 被称为ZipCode(最初被定义为整数),改变成一个10字符文本字段:
    ALTER TABLE Employees ALTER COLUMN ZipCode TEXT(10)


    使用 ADD CONSTRAINT 添加多重字段索引。关于多重字段索引的详细信息,请参阅 CONSTRAINT 子句主题。
    使用 DROP COLUMN 删除字段。只要指定欲删除的字段名即可。
    使用 DROP CONSTRAINT 删除多重字段索引。只要在 CONSTRAINT 保留字后面指定索引名即可。
    --------------------------------------------------------------------------------

    注意

    不能同时添加或删除一个以上的字段或索引。
    你可以使用 CREATE INDEX 语句在一个表中增加一个单字段或多重字段,你还可以使用 ALTER TABLE 或 DROP 语句删除一个由ALTER TABLE或CREATE INDEX建立的索引。
    可以在单一字段上使用 NOT NULL,或在用于单一字段或多重字段(名为 CONSTRAINT) 的 CONSTRAINT 子句中使用 NOT NULL。但是,一个字段只能使用一次 NOT NULL 限制。尝试多次应用此限制将导致运行错误。

    注意

    展开全文
  • 当有些数据删除后,不需要将其在数据库删掉,而仅仅是添加一个字段deleted来作为删除标识; 1.在application.yml文件添加 mybatis-plus: `global-config: db-config: logic-not-delete-value: 0 //代表逻辑未...
  • 数据库字段命名规范

    万次阅读 2018-03-09 10:15:09
    1.数据库命名规范:(1)表名前应该加上前缀,的前缀一个用系统或模块的英文名称缩写,前缀全部大写或首字母大写,表名包含的单词首字母大写。(2)数据库表名应该有意义,并且易于理解,最好使用可以表达功能的...
  • 如果使用Spring jpa定义表中的长度 使员工注册在:@Column(columnDefinition="类型(字段的长度)"); @Column(columnDefinition = "varchar(500) ") 使用注解的时候必须在getXXX()方法的上面才起作用, 如果...
  • 改(根据ID更新员工,根据员工ID删除部门和员工表中的数据,再次插入新数据到部门和员工关系表中) --根据员工ID更新员工表和关系 create proc myUpdate (  @EmployeeID int  ,@DepartmentID ...
  • 1.数据库设计14规则 1. 原始单据与实体之间的关系   可以是一对一、一对多、多对多的关系。在一般情况下,它们是一对一的关系:即一张原始单据对应且只对应一个实体。... 〖例1〗:一份员工
  • 在这里,从SQL SERVER 2008的一本书模仿了其中员工管理数据库的数据,同时随机增加了数据项,同时添加一些自己觉得比较重要的知识点,从基础做起,把日常能用到的数据库语句用一遍,让有需要的小白可以多练练手,...
  • 数据库设计之设计字段

    千次阅读 2013-06-06 19:40:06
    数据库设计技巧系列--设计字段 设计字段 1. 检查各种变化 我在设计数据库的时候会考虑到哪些数据字段将来可能会发生变更。比方说,姓氏就是如此(注意是西方人的姓氏,比如女性结婚后从夫姓等)。所以,...
  • oracle创建增加字段sql脚本

    千次阅读 2017-08-23 11:06:46
    oracle中用comment on命令给字段加以说明,语法如下: COMMENT ON { TABLE [ schema. ] { table | view } | COLUMN [ schema. ] { table. | view. | materialized_view. } column | OPERA
  • 数据库的字段命名规范1.数据库命名规范:(1)表名前应该加上前缀,的前缀一个用系统或模块的英文名称缩写,前缀全部大写或首字母大写,表名包含的单词首字母大写。(2)数据库表名应该有意义,并且易于理解,...
  • 怎样添加或删除字段

    千次阅读 2011-09-20 16:19:32
    如要在一个hwsp表中填加字段:ylxs alter table hwsp add column ylxs ALTER TABLE {ADD ADD{COLUMN 字段类型 [ (字长)] [NOT NULL] [CONSTRAINT 索引 ] |
  • 数据库设计 - 设计字段

    千次阅读 2010-08-11 09:34:00
    检查各种变化我在设计数据库的时候会考虑到哪些数据字段将来可能会发生变更。比方说,姓氏就是如此(注 意是西方人的姓氏,比如女性结婚后从夫姓等)。所以,在建立系统存储客户信息时,我倾向于 ...
  • 操作的重要内容:   操作重要内容:# 查看  # 添加数据  # 删除数据  # 修改数据  # 浏览数据
  • 1.数据库命名规范: (1)表名前应该加上前缀,的前缀一个用系统或模块的英文名称缩写,前缀全部大写或首字母大写,表名包含的单词首字母大写。...(4)在数据库命名时应该用英文单词的单数形式,如员工表命...
  • 设计字段1. 检查各种变化我在设计数据库的时候会考虑到哪些数据字段将来可能会发生变更。比方说,姓氏就是如此(注意是西方人的姓氏,比如女性结婚后从夫姓等)。所以,在建立系统存储客户信息时,我倾向于在...
  • SQL Server数据库设计字段的经验

    千次阅读 2009-09-29 11:42:00
    检查各种变化 在设计数据库的时候会考虑到哪些数据字段将来可能会发生变更。比方说,姓氏就是如此(注意是西方人的姓氏,比如女性结婚后从夫姓等)。所以,在建立系统存储客户信息时,我倾向于在单独的一个数据表里...
  • 约束条件 作用控制如何给字段赋值 NULL | key | default | extra 是否允许赋null值 键值 默认值 额外设置  null(允许为空) not(不允许为空) key(索引类型) default(设置默认值,缺省为null) crea...
  • 第2 部分— 设计字段1. 检查各种变化我在设计数据库的时候会考虑到哪些数据字段将来可能会发生变更。比方说,姓氏就是如此(注意是西方人的姓氏,比如女性结婚后从夫姓等)。所以,在建立系统存储客户信息时,我...
  • MySQL给表里的某个字段建立索引

    千次阅读 2020-08-24 14:40:03
    注:`table_name`和`column`的``是Esc下方的一个键~`,可省略不写``,直接写表名和字段名 1.添加PRIMARY KEY(主键索引): ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 2.添加UNIQUE(唯一索引): ...
  • 数据库设计字段命名规范(转)

    千次阅读 2011-11-22 09:09:27
    (1)表名前应该加上前缀,的前缀一个用系统或模块的英文名称缩写,前缀全部大写或首字母大写,表名包含的单词首字母大写。 (2)数据库表名应该有意义,并且易于理解,最好使用可以表达功能的英文单词或缩写,如果...
  • 本章主要内容:创建数据透视,更改数据透视汇总方式,数据透视表中的组合,汇总多列数据,创建计算字段,生成多张工作

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 42,374
精华内容 16,949
关键字:

删除员工表中的字段