主外键补充

一张表里只能有一个主键,
主键可以由多列组成

CREATE TABLE product (
    category INT NOT NULL, 
    id INT NOT NULL,
    price DECIMAL,
    PRIMARY KEY(category, id)
)   ENGINE=INNODB;

CREATE TABLE customer (
    id INT NOT NULL,
    PRIMARY KEY (id)
)   ENGINE=INNODB;

CREATE TABLE product_order (
    no INT NOT NULL AUTO_INCREMENT,
    product_category INT NOT NULL,
    product_id INT NOT NULL,
    customer_id INT NOT NULL,

    PRIMARY KEY(no),
    INDEX (product_category, product_id),
    INDEX (customer_id),

    FOREIGN KEY (product_category, product_id)
      REFERENCES product(category, id)
      ON UPDATE CASCADE ON DELETE RESTRICT,

    FOREIGN KEY (customer_id)
      REFERENCES customer(id)
)   ENGINE=INNODB;

https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html

一对一

create table userinfo1(
    id int auto_increment primary key,
    name char(10),
    gender char(10),
    email varchar(64)
)engine=innodb default charset=utf8;

create table admin(
    id int not null auto_increment primary key,
    username varchar(64) not null,
    password VARCHAR(64) not null,
    user_id int not null,

    unique uq_u1 (user_id),
    CONSTRAINT fk_admin_u1 FOREIGN key (user_id) REFERENCES userinfo1(id)
)engine=innodb default charset=utf8;

多对多

create table userinfo2(
    id int auto_increment primary key,
    name char(10),
    gender char(10),
    email varchar(64)
)engine=innodb default charset=utf8;

create table host(
    id int auto_increment primary key,
    hostname char(64)
)engine=innodb default charset=utf8;

create table user2host(
    id int auto_increment primary key,
    userid int not null,
    hostid int not null,

    unique uq_user_host (userid,hostid),
    CONSTRAINT fk_u2h_user FOREIGN key (userid) REFERENCES userinfo2(id),
    CONSTRAINT fk_u2h_host FOREIGN key (hostid) REFERENCES host(id)
)engine=innodb default charset=utf8;
                    

自增和步长

一次登录是一次会话

# 基于会话级别
mysql> show session variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
2 rows in set, 1 warning (0.00 sec)

# 基于全局级别
mysql> show global variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
2 rows in set, 1 warning (0.00 sec)

# 设置会话步长
set session auto_increment_offset=10;

# 设置全局步长
set global auto_increment_offset=10;