MySQL 8.0创建使用通用表空间

最新推荐文章于 2025-03-24 20:15:38 发布

浅夏和风

最新推荐文章于 2025-03-24 20:15:38 发布

阅读量334

点赞数

分类专栏： # MySQL 文章标签： mysql 数据库

本文链接：https://blog.youkuaiyun.com/oracle_neo/article/details/131815452

版权

MySQL 专栏收录该内容

2 篇文章

订阅专栏

使用数据目录存放表空间。

root [(none)]> create tablespace test_01 add datafile 'test_01.ibd';
Query OK, 0 rows affected (0.11 sec)

root [(none)]> use test
Database changed

root [test]> create table test01(id int) tablespace = test_01;
Query OK, 0 rows affected (0.01 sec)

root [test]> insert into test01 values (10), (20);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

root [test]> show create table test01\G
*************************** 1. row ***************************
       Table: test01
Create Table: CREATE TABLE `test01` (
  `id` int DEFAULT NULL
) /*!50100 TABLESPACE `test_01` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

root [test]> show global variables like 'innodb_directories';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_directories |       |
+--------------------+-------+
1 row in set (0.00 sec)

表空间指定数据目录外的磁盘

修改参数

innodb_directories = /data/mysql_data_3307_tablespace

重启DB

查看参数

root [(none)]> show global variables like 'innodb_directories';
+--------------------+----------------------------------+
| Variable_name      | Value                            |
+--------------------+----------------------------------+
| innodb_directories | /data/mysql_data_3307_tablespace |
+--------------------+----------------------------------+
1 row in set (0.01 sec)

root [(none)]> use test
Database changed
root [test]> create table test02(id int) tablespace test_02;
Query OK, 0 rows affected (0.02 sec)

root [test]> insert into test02 values (10), (20);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

root [test]> show create table test02\G
*************************** 1. row ***************************
       Table: test02
Create Table: CREATE TABLE `test02` (
  `id` int DEFAULT NULL
) /*!50100 TABLESPACE `test_02` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

root [test]> system ls /data/mysql_data_3307_tablespace
test21.ibd