MySQL笔记，五MySQL 角色与SQL CHECK约束

MySQL ROLE

MySQL 8.0 Reference Manual / Security / MySQL User Account Management / Using Roles

how to create role on MySQL database

mysql 8.0 才支持角色。

创建新角色：

CREATE ROLE \'app_developer\', \'app_read\', \'app_write\';

将权限赋予角色：

GRANT ALL ON app_db.* TO \'app_developer\';
GRANT SELECT ON app_db.* TO \'app_read\';
GRANT INSERT, UPDATE, DELETE ON app_db.* TO \'app_write\';

创建新用户：

CREATE USER \'dev1\'@\'localhost\' IDENTIFIED BY \'dev1pass\';
CREATE USER \'read_user1\'@\'localhost\' IDENTIFIED BY \'read_user1pass\';
CREATE USER \'read_user2\'@\'localhost\' IDENTIFIED BY \'read_user2pass\';
CREATE USER \'rw_user1\'@\'localhost\' IDENTIFIED BY \'rw_user1pass\';

将角色赋予用户：

GRANT \'app_developer\' TO \'dev1\'@\'localhost\';
GRANT \'app_read\' TO \'read_user1\'@\'localhost\', \'read_user2\'@\'localhost\';
GRANT \'app_read\', \'app_write\' TO \'rw_user1\'@\'localhost\';

SQL CHECK 约束

http://www.w3school.com.cn/sql/sql_check.asp

MySQL关于check约束无效的解决办法

没找到官方文档。查了下资料发现 MySQL 不支持 CHECK ，加不加都一样。。

示例：

DROP TABLE IF EXISTS employee;
CREATE TABLE employee (
    name VARCHAR(50) NOT NULL,
    phone_num VARCHAR(20) NOT NULL,
    id CHAR(8),
    sex CHAR(1),
    
    PRIMARY KEY(id),
    
    CONSTRAINT check_sex CHECK (sex IN (\'f\', \'m\')),
    CONSTRAINT check_id CHECK (id LIKE \'E_______\'))    ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO employee
    (name, 
    phone_num, 
    id, 
    sex)
VALUES
    (\'李四\',
    \'15159000122\',
    \'E2008001\',
    \'f\');
    
DROP TABLE IF EXISTS product;
CREATE TABLE product (
    name VARCHAR(50) NOT NULL,
    id CHAR(9),
    product_type VARCHAR(50) NOT NULL,
    production_date DATE,
    
    PRIMARY KEY(id),
    
    CONSTRAINT check_id CHECK (id LIKE \'P%\'
    AND SUBSTRING(id, 2, 4) = YEAR(production_date)))    ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO product
    (name, 
    id, 
    product_type, 
    production_date)
VALUES
    (\'飞碟迷宫\',
    \'P20080021\',
    \'玩具\',
    \'2008-10-22\');

CREATE TABLE customer (
    name VARCHAR(50) NOT NULL,
    id CHAR(9),
    sex CHAR(1) NOT NULL,
    department VARCHAR(50) NOT NULL,
    salary VARCHAR(50) NOT NULL,
    job_title VARCHAR(50) NOT NULL,
    
    PRIMARY KEY(id),
    
    CONSTRAINT check_id CHECK (id LIKE \'C%\')
    
)    ENGINE=INNODB DEFAULT CHARSET=utf8;    

CREATE TABLE order_master (
    id CHAR(12) PRIMARY KEY,
    customer_id CHAR(9) NOT NULL,
    employee_id CHAR(8) NOT NULL,
    total DECIMAL(8, 2) DEFAULT 0,
    -- order_date DATE DEFAULT CURDATE(), 这两行是错的，MySQL目前字段的默认值不支持函数
    -- shipping_date DATE DEFAULT CURDATE(), 另外一种解决方案是由应用程序插入默认值
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -- shipping_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
    -- 或者是用触发器来做
    invoice_number VARCHAR(50) UNIQUE,
    
    FOREIGN KEY (customer_id) 
        REFERENCES customer(id),
    
    FOREIGN KEY (employee_id) 
        REFERENCES employee(id)        
)    ENGINE=INNODB DEFAULT CHARSET=utf8;    

CREATE TABLE order_detail (
    order_id CHAR(12) NOT NULL,
    product_id CHAR(9) NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(8, 2),
    
    FOREIGN KEY (order_id) 
        REFERENCES order_master(id),
    
    FOREIGN KEY (product_id) 
        REFERENCES product(id)    
)    ENGINE=INNODB DEFAULT CHARSET=utf8;