MYSQL 8インストールの02(データベースおよび表領域の作成)

3540 ワード

  • DB
  • を作成
    [root@XAG8 data]# mysql.login
    Enter password: 
    
    [email protected] : (none)【11:12:44】5 SQL->CREATE DATABASE TESTDB;
    
    [email protected] : (none)【11:12:47】6 SQL->SELECT DATABASE();
    +------------+
    | DATABASE() |
    +------------+
    | NULL       |
    +------------+
    
    [email protected] : (none)【11:13:02】7 SQL->use TESTDB;
    
    [email protected] : TESTDB【11:13:30】8 SQL->SELECT DATABASE();
    +------------+
    | DATABASE() |
    +------------+
    | testdb     |
    +------------+
    
    [root@XAG8 testdb]# pwd
    /usr/local/mysql/data/testdb
    [root@XAG8 testdb]# ls
    
    #       
    [email protected] : TESTDB【11:22:31】18 SQL->
    create tablespace testdb_ud ADD DATAFILE '/usr/local/mysql/data/testdb_ud.ibd' Engine=InnoDB;
    
    #         
    [email protected] : TESTDB【11:39:33】27 SQL->
    create table t1 (aaa int,bbb varchar(10)) tablespace testdb_ud;
    
  • パーティションテーブル
  • を作成する
    create tablespace testdb201907 add datafile '/usr/local/mysql/data/testdb201907.ibd' Engine=InnoDB;
    
    create tablespace testdb201908 add datafile '/usr/local/mysql/data/testdb201908.ibd' Engine=InnoDB;
    
    create tablespace testdb201909 add datafile '/usr/local/mysql/data/testdb201909.ibd' Engine=InnoDB;
    
    create tablespace testdb201910 add datafile '/usr/local/mysql/data/testdb201910.ibd' Engine=InnoDB;
    
    [email protected] : TESTDB【05:09:20】49 SQL->
        create table t2_part
        (
         id int,
         creat_time datetime
        ) ENGINE=InnoDB PARTITION BY RANGE(TO_DAYS(creat_time))
        (
        PARTITION pl907 VALUES LESS THAN (TO_DAYS('2019-08-01')),
        PARTITION p1908 VALUES LESS THAN (TO_DAYS('2019-09-01')),
        PARTITION p1909 VALUES LESS THAN (TO_DAYS('2019-10-01'))
        );
    
    [email protected] : TESTDB【06:00:27】114 SQL->
        create table t3_part
        (
         id int,
          creat_date date
        ) ENGINE=InnoDB PARTITION BY RANGE columns(creat_date)
        (
        PARTITION pl907 VALUES LESS THAN ('2019-08-01'),
        PARTITION p1908 VALUES LESS THAN ('2019-09-01'),
        PARTITION p1909 VALUES LESS THAN ('2019-10-01')
        );
    
    
    [email protected] : TESTDB【06:17:34】139 SQL->create table t5_part
         (
          id int,
          creat_time datetime
         ) ENGINE=InnoDB PARTITION BY RANGE(TO_DAYS(creat_time))
         (
         PARTITION pl907 VALUES LESS THAN (TO_DAYS('2019-08-01')) TABLESPACE testdb201907,
         PARTITION p1908 VALUES LESS THAN (TO_DAYS('2019-09-01')) TABLESPACE testdb201908,
         PARTITION p1909 VALUES LESS THAN (TO_DAYS('2019-10-01')) TABLESPACE testdb201909
         );
    ERROR 1478 (HY000): InnoDB : A partitioned table is not allowed in a shared tablespace.
    # MySQL 5.7.24                ,  MySQL 8.0.13    。 
    #       InnoDB           。
    
    
    [email protected] : TESTDB【05:45:45】96 SQL->
    SELECT a.NAME AS space_name, b.NAME AS table_name FROM INFORMATION_SCHEMA.INNODB_TABLESPACES a,INFORMATION_SCHEMA.INNODB_TABLES b WHERE a.SPACE=b.SPACE AND a.NAME LIKE 'testdb%';
    +------------------------+------------------------+
    | space_name             | table_name             |
    +------------------------+------------------------+
    | testdb_ud              | testdb/t1              |
    | testdb/t2_part?p?pl907 | testdb/t2_part#p#pl907 |
    | testdb/t2_part?p?p1908 | testdb/t2_part#p#p1908 |
    | testdb/t2_part?p?p1909 | testdb/t2_part#p#p1909 |
    | testdb/t3_part?p?pl907 | testdb/t3_part#p#pl907 |
    | testdb/t3_part?p?p1908 | testdb/t3_part#p#p1908 |
    | testdb/t3_part?p?p1909 | testdb/t3_part#p#p1909 |
    +------------------------+------------------------+