以我的windows環(huán)境為例:
成都創(chuàng)新互聯(lián)公司是一家集網(wǎng)站建設(shè),余慶企業(yè)網(wǎng)站建設(shè),余慶品牌網(wǎng)站建設(shè),網(wǎng)站定制,余慶網(wǎng)站建設(shè)報(bào)價(jià),網(wǎng)絡(luò)營(yíng)銷,網(wǎng)絡(luò)優(yōu)化,余慶網(wǎng)站推廣為一體的創(chuàng)新建站企業(yè),幫助傳統(tǒng)企業(yè)提升企業(yè)形象加強(qiáng)企業(yè)競(jìng)爭(zhēng)力??沙浞譂M足這一群體相比中小企業(yè)更為豐富、高端、多元的互聯(lián)網(wǎng)需求。同時(shí)我們時(shí)刻保持專業(yè)、時(shí)尚、前沿,時(shí)刻以成就客戶成長(zhǎng)自我,堅(jiān)持不斷學(xué)習(xí)、思考、沉淀、凈化自己,讓我們?yōu)楦嗟钠髽I(yè)打造出實(shí)用型網(wǎng)站。
在my.ini [mysqld]下面增加
lower_case_table_names=0
就可以新建大寫表名的表了
也可以rename table 表名 to 新表名 去改現(xiàn)有的表
改時(shí)注意 要先改成一個(gè)不存在的表 再改回全部大寫的表
比如你要把test改成TEST
先rename table test to test1
再rename table test1 to TEST
對(duì)用戶來說,分區(qū)表是一個(gè)獨(dú)立的邏輯表,但是底層由多個(gè)物理子表組成,實(shí)現(xiàn)分區(qū)的代碼實(shí)際上是通過對(duì)一組底層表的對(duì)象封裝,但對(duì)SQL層來說是一個(gè)完全封裝底層的黑盒子。
MySQL實(shí)現(xiàn)分區(qū)的方式也意味著索引也是按照分區(qū)的子表定義, 沒有全局索引 。
分區(qū)的意思是指將同一表中不同行的記錄分配到不同的物理文件中 ,幾個(gè)分區(qū)就有幾個(gè).idb文件。MySQL數(shù)據(jù)庫(kù)的分區(qū)是局部分區(qū)索引,一個(gè)分區(qū)中既存了數(shù)據(jù),又放了索引。也就是說,每個(gè)區(qū)的聚集索引和非聚集索引都放在各自區(qū)的(不同的物理文件)。
1、可以讓單表 存儲(chǔ)更多的數(shù)據(jù) 。
2、 分區(qū)表的數(shù)據(jù)更容易維護(hù) ,可以通過刪除與那些數(shù)據(jù)有關(guān)的分區(qū),更容易刪除數(shù)據(jù),也可以增加新的分區(qū)來支持新插入的數(shù)據(jù)。另外,還可以對(duì)一個(gè)獨(dú)立分區(qū)進(jìn)行優(yōu)化、檢查、修復(fù)等操作。
3、部分查詢能夠從查詢條件確定只落在少數(shù)分區(qū)上, 查詢速度會(huì)很快 。
4、通過跨多個(gè)磁盤來分散數(shù)據(jù)查詢,來 獲得更大的查詢吞吐量 。
要使定時(shí)事件起作用,MySQL的常量GLOBAL event_scheduler必須為on或者是1。
1、查看scheduler的當(dāng)前狀態(tài):
2、修改scheduler狀態(tài)為打開(0:off , 1:on):
3、臨時(shí)打開定時(shí)器(四種方法):
4、永久生效的方法,修改配置文件my.cnf
5、臨時(shí)開啟某個(gè)事件
6、臨時(shí)關(guān)閉某個(gè)事件
不在數(shù)據(jù)庫(kù)全文索引
對(duì)我有用[0]丟個(gè)板磚[0]引用舉報(bào)管理TOPmumubangditu(木木邦迪兔)等
級(jí):
0. 目標(biāo)端必須有同名表,沒有則建一個(gè)空表;
####################################
1、 源端文件準(zhǔn)備
源端:?
flush tables t for export;?
復(fù)制?
t.ibd, t.cfg到目標(biāo)端。?
###############################
flush tables tt7? ?for export;?
cp? tt7*? ?../ops
2、 目標(biāo)端存在同樣的表則丟棄原來的數(shù)據(jù)文件
目標(biāo)端:?
alter table tt7? discard tablespace;
3、 目標(biāo)端加載新的數(shù)據(jù)文件 t.ibd
alter table tt7 import tablespace;?
4、源端釋放鎖
源端:?
unlock tables;?
過程中主要異常處理:
#####################################################
SELECT? * FROM? ?ops2.tt7? ? ;
SELECT? * FROM? ?ops.tt7? ? ;
import tablespace報(bào)錯(cuò):
mysql alter table tt7 import tablespace;?
ERROR 1812 (HY000): Tablespace is missing for table ops.tt7.
確認(rèn)再相應(yīng)的目錄存在兩個(gè)文件
確認(rèn)屬主和權(quán)限
#####################################################
過程
[root@qaserver120 ops]# ll
total 80
drwxr-xr-x 2 root? root? ? ? 36 Dec? 2 21:42 000
-rw-r----- 1 mysql mysql 114688 Dec? 2 21:17 tt2.ibd
[root@qaserver120 ops]#?
[root@qaserver120 ops]#?
[root@qaserver120 ops]#?
[root@qaserver120 ops]# cp 000
[root@qaserver120 ops]# ll
drwxr-xr-x 2 root? root? ? ? 36 Dec? 2 21:42 000
-rw-r----- 1 mysql mysql 114688 Dec? 2 21:17 tt2.ibd
-rw-r----- 1 root? root? ? ?627 Dec? 2 21:45 tt7.cfg
-rw-r----- 1 root? root? 114688 Dec? 2 21:45 tt7.ibd
[root@qaserver120 ops]# chown mysql.mysql tt7*
[root@qaserver120 ops]#?
[root@qaserver120 ops]# ll
drwxr-xr-x 2 root? root? ? ? 36 Dec? 2 21:42 000
-rw-r----- 1 mysql mysql 114688 Dec? 2 21:17 tt2.ibd
-rw-r----- 1 mysql mysql? ? 627 Dec? 2 21:45 tt7.cfg
-rw-r----- 1 mysql mysql 114688 Dec? 2 21:46 tt7.ibd
[root@qaserver120 ops]#?
#####################################################
mysql show tables;
+---------------+
| Tables_in_ops |
+---------------+
| tt2? ? ? ? ? ?|
| tt7? ? ? ? ? ?|
+---------------+
2 rows in set (0.00 sec)
mysql select * from tt7;
ERROR 1814 (HY000): Tablespace has been discarded for table 'tt7'
mysql alter table tt7 import tablespace;?
ERROR 1812 (HY000): Tablespace is missing for table ops.tt7.
mysql?
mysql alter table tt7 import tablespace;
Query OK, 0 rows affected (0.08 sec)
mysql?
mysql select * from tt7;
+--------------+------+
| x? ? ? ? ? ? | y? ? |
+--------------+------+
| BBBBBB? ? ? ?| NULL |
| AAAAAA? ? ? ?| NULL |
| BBBBBB? ? ? ?| NULL |
| 555555555555 | NULL |
| AAAAAA? ? ? ?| NULL |
| BBBBBB? ? ? ?| NULL |
| 555555555555 | NULL |
+--------------+------+
7 rows in set (0.00 sec)
mysql
###############################################
################################################
mysql mysql show tables;
+----------------+
| Tables_in_ops2 |
+----------------+
| tt2? ? ? ? ? ? |
| tt3? ? ? ? ? ? |
| tt7? ? ? ? ? ? |
+----------------+
3 rows in set (0.00 sec)
mysql?
mysql use ops
Database changed
mysql show tables;
+---------------+
| Tables_in_ops |
+---------------+
| tt2? ? ? ? ? ?|
+---------------+
1 row in set (0.00 sec)
mysql?
mysql use ops2;
Database changed
mysql select * from tt7;
+--------+------+
| x? ? ? | y? ? |
+--------+------+
| BBBBBB | NULL |
+--------+------+
1 row in set (0.00 sec)
mysql?
mysql?
mysql insert into tt7 select * from tt3;
Query OK, 3 rows affected (0.00 sec)
Records: 3? Duplicates: 0? Warnings: 0
mysql insert into tt7 select * from tt3;
Query OK, 3 rows affected (0.00 sec)
Records: 3? Duplicates: 0? Warnings: 0
mysql select * from tt7;
+--------------+------+
| x? ? ? ? ? ? | y? ? |
+--------------+------+
| BBBBBB? ? ? ?| NULL |
| AAAAAA? ? ? ?| NULL |
| BBBBBB? ? ? ?| NULL |
| 555555555555 | NULL |
| AAAAAA? ? ? ?| NULL |
| BBBBBB? ? ? ?| NULL |
| 555555555555 | NULL |
+--------------+------+
7 rows in set (0.00 sec)
mysql?
mysql commit;
Query OK, 0 rows affected (0.00 sec)
mysql?
mysql exit
Bye
[root@qaserver120 pkg]# cd /data/mysql/ops2
[root@qaserver120 ops2]# ll
total 240
-rw-r----- 1 mysql mysql 114688 Dec? 2 21:17 tt2.ibd
-rw-r----- 1 mysql mysql 114688 Dec? 2 21:17 tt3.ibd
-rw-r----- 1 mysql mysql 114688 Dec? 2 21:36 tt7.ibd
[root@qaserver120 ops2]#?
[root@qaserver120 ops2]#?
[root@qaserver120 ops2]#?
[root@qaserver120 ops2]# mysql -u'root'? -p'fgxkB9;Zq40^MFQUi$PJ'? ? ? ? -A
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.? Commands end with ; or \g.
Your MySQL connection id is 56
Server version: 8.0.18 MySQL Community Server - GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql use ops2
Database changed
mysql?
mysql flush tables tt7? ?for export;?
Query OK, 0 rows affected (0.00 sec)
mysql show tables;
+----------------+
| Tables_in_ops2 |
+----------------+
| tt2? ? ? ? ? ? |
| tt3? ? ? ? ? ? |
| tt7? ? ? ? ? ? |
+----------------+
3 rows in set (0.01 sec)
mysql exit
Bye
[root@qaserver120 ops2]# ll
total 240
-rw-r----- 1 mysql mysql 114688 Dec? 2 21:17 tt2.ibd
-rw-r----- 1 mysql mysql 114688 Dec? 2 21:17 tt3.ibd
-rw-r----- 1 mysql mysql 114688 Dec? 2 21:36 tt7.ibd
[root@qaserver120 ops2]# ll
total 240
-rw-r----- 1 mysql mysql 114688 Dec? 2 21:17 tt2.ibd
-rw-r----- 1 mysql mysql 114688 Dec? 2 21:17 tt3.ibd
-rw-r----- 1 mysql mysql 114688 Dec? 2 21:36 tt7.ibd
[root@qaserver120 ops2]# pwd
/data/mysql/ops2
[root@qaserver120 ops2]# cd? cd /data/mysql?
-bash: cd: cd: No such file or directory
[root@qaserver120 ops2]#? cd /data/mysql/ops2
[root@qaserver120 ops2]# ll
total 240
-rw-r----- 1 mysql mysql 114688 Dec? 2 21:17 tt2.ibd
-rw-r----- 1 mysql mysql 114688 Dec? 2 21:17 tt3.ibd
-rw-r----- 1 mysql mysql 114688 Dec? 2 21:36 tt7.ibd
[root@qaserver120 ops2]# ll -al
total 244
drwxr-x---? 2 mysql mysql? ? ?51 Dec? 2 21:38 .
drwxr-xr-x 12 mysql mysql? ?4096 Dec? 2 21:17 ..
-rw-r-----? 1 mysql mysql 114688 Dec? 2 21:17 tt2.ibd
-rw-r-----? 1 mysql mysql 114688 Dec? 2 21:17 tt3.ibd
-rw-r-----? 1 mysql mysql 114688 Dec? 2 21:36 tt7.ibd
[root@qaserver120 ops2]# pwd
/data/mysql/ops2
[root@qaserver120 ops2]# mysql -u'root'? -p'fgxkB9;Zq40^MFQUi$PJ'? ? ? ? -A
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.? Commands end with ; or \g.
Your MySQL connection id is 57
Server version: 8.0.18 MySQL Community Server - GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql use ops2
Database changed
mysql show tables;
+----------------+
| Tables_in_ops2 |
+----------------+
| tt2? ? ? ? ? ? |
| tt3? ? ? ? ? ? |
| tt7? ? ? ? ? ? |
+----------------+
3 rows in set (0.00 sec)
mysql select * from tt7;
+--------------+------+
| x? ? ? ? ? ? | y? ? |
+--------------+------+
| BBBBBB? ? ? ?| NULL |
| AAAAAA? ? ? ?| NULL |
| BBBBBB? ? ? ?| NULL |
| 555555555555 | NULL |
| AAAAAA? ? ? ?| NULL |
| BBBBBB? ? ? ?| NULL |
| 555555555555 | NULL |
+--------------+------+
7 rows in set (0.00 sec)
mysql flush tables tt7? ?for export;?
Query OK, 0 rows affected (0.00 sec)
mysql use ops
Database changed
mysql ll
- ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'll' at line 1
mysql show tables;
+---------------+
| Tables_in_ops |
+---------------+
| tt2? ? ? ? ? ?|
+---------------+
1 row in set (0.00 sec)
mysql?
mysql?
mysql alter table tt7 import tablespace;?
ERROR 1100 (HY000): Table 'tt7' was not locked with LOCK TABLES
mysql?
mysql?
mysql use ops2
Database changed
mysql show tables;
+----------------+
| Tables_in_ops2 |
+----------------+
| tt2? ? ? ? ? ? |
| tt3? ? ? ? ? ? |
| tt7? ? ? ? ? ? |
+----------------+
3 rows in set (0.00 sec)
mysql select * from tt7;
+--------------+------+
| x? ? ? ? ? ? | y? ? |
+--------------+------+
| BBBBBB? ? ? ?| NULL |
| AAAAAA? ? ? ?| NULL |
| BBBBBB? ? ? ?| NULL |
| 555555555555 | NULL |
| AAAAAA? ? ? ?| NULL |
| BBBBBB? ? ? ?| NULL |
| 555555555555 | NULL |
+--------------+------+
7 rows in set (0.00 sec)
mysql unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql show create table? tt7;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?|
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tt7? ?| CREATE TABLE `tt7` (
`x` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs DEFAULT NULL,
`y` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_cs |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql use ops
Database changed
mysql show tables;
+---------------+
| Tables_in_ops |
+---------------+
| tt2? ? ? ? ? ?|
+---------------+
1 row in set (0.01 sec)
mysql? CREATE TABLE `tt7` (
-? ?`x` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs DEFAULT NULL,
-? ?`y` int(11) DEFAULT NULL
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_cs ;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql?
mysql?
mysql show tables;
+---------------+
| Tables_in_ops |
+---------------+
| tt2? ? ? ? ? ?|
| tt7? ? ? ? ? ?|
+---------------+
2 rows in set (0.00 sec)
mysql select * from tt7;
Empty set (0.00 sec)
mysql?
mysql alter table tt7? discard tablesapce;?
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'tablesapce' at line 1
mysql alter table tt7? discard tablespace;
Query OK, 0 rows affected (0.03 sec)
mysql?
mysql?
mysql show tables;
+---------------+
| Tables_in_ops |
+---------------+
| tt2? ? ? ? ? ?|
| tt7? ? ? ? ? ?|
+---------------+
2 rows in set (0.00 sec)
mysql select * from tt7;
ERROR 1814 (HY000): Tablespace has been discarded for table 'tt7'
mysql?
mysql?
mysql?
mysql show tables;
+---------------+
| Tables_in_ops |
+---------------+
| tt2? ? ? ? ? ?|
| tt7? ? ? ? ? ?|
+---------------+
2 rows in set (0.00 sec)
mysql select * from tt7;
ERROR 1814 (HY000): Tablespace has been discarded for table 'tt7'
mysql?
mysql?
mysql alter table tt7 import tablespace;?
ERROR 1812 (HY000): Tablespace is missing for table `ops`.`tt7`.
mysql?
mysql?
mysql?
mysql alter table tt7 import tablespace;
Query OK, 0 rows affected (0.08 sec)
mysql?
mysql?
mysql select * from tt7;
+--------------+------+
| x? ? ? ? ? ? | y? ? |
+--------------+------+
| BBBBBB? ? ? ?| NULL |
| AAAAAA? ? ? ?| NULL |
| BBBBBB? ? ? ?| NULL |
| 555555555555 | NULL |
| AAAAAA? ? ? ?| NULL |
| BBBBBB? ? ? ?| NULL |
| 555555555555 | NULL |
+--------------+------+
7 rows in set (0.00 sec)
mysql SELECT? * FROM? ?ops2.tt7? ? ;
+--------------+------+
| x? ? ? ? ? ? | y? ? |
+--------------+------+
| BBBBBB? ? ? ?| NULL |
| AAAAAA? ? ? ?| NULL |
| BBBBBB? ? ? ?| NULL |
| 555555555555 | NULL |
| AAAAAA? ? ? ?| NULL |
| BBBBBB? ? ? ?| NULL |
| 555555555555 | NULL |
+--------------+------+
7 rows in set (0.00 sec)
mysql SELECT? * FROM? ?ops.tt7? ? ;
+--------------+------+
| x? ? ? ? ? ? | y? ? |
+--------------+------+
| BBBBBB? ? ? ?| NULL |
| AAAAAA? ? ? ?| NULL |
| BBBBBB? ? ? ?| NULL |
| 555555555555 | NULL |
| AAAAAA? ? ? ?| NULL |
| BBBBBB? ? ? ?| NULL |
| 555555555555 | NULL |
+--------------+------+
7 rows in set (0.00 sec)
mysql?
mysql?
mysql?
mysql unlock tables;?
Query OK, 0 rows affected (0.00 sec)
mysql unlock tables;?
Query OK, 0 rows affected (0.00 sec)
mysql use ops
Database changed
mysql show tables;
+---------------+
| Tables_in_ops |
+---------------+
| tt2? ? ? ? ? ?|
| tt7? ? ? ? ? ?|
+---------------+
2 rows in set (0.00 sec)
mysql?
mysql?
mysql?
mysql use ops2;
Database changed
mysql?
mysql?
mysql show tables;
+----------------+
| Tables_in_ops2 |
+----------------+
| tt2? ? ? ? ? ? |
| tt3? ? ? ? ? ? |
| tt7? ? ? ? ? ? |
+----------------+
3 rows in set (0.01 sec)
mysql?
mysql select * from tt7;
+--------------+------+
| x? ? ? ? ? ? | y? ? |
+--------------+------+
| BBBBBB? ? ? ?| NULL |
| AAAAAA? ? ? ?| NULL |
| BBBBBB? ? ? ?| NULL |
| 555555555555 | NULL |
| AAAAAA? ? ? ?| NULL |
| BBBBBB? ? ? ?| NULL |
| 555555555555 | NULL |
+--------------+------+
7 rows in set (0.00 sec)
mysql?
mysql use ops;
Database changed
mysql?
mysql?
mysql select * from tt7;
+--------------+------+
| x? ? ? ? ? ? | y? ? |
+--------------+------+
| BBBBBB? ? ? ?| NULL |
| AAAAAA? ? ? ?| NULL |
| BBBBBB? ? ? ?| NULL |
| 555555555555 | NULL |
| AAAAAA? ? ? ?| NULL |
| BBBBBB? ? ? ?| NULL |
| 555555555555 | NULL |
+--------------+------+
7 rows in set (0.00 sec)
網(wǎng)頁(yè)標(biāo)題:mysql怎么變成大表 mysql 表 大小
網(wǎng)頁(yè)URL:http://jinyejixie.com/article16/dopcigg.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供面包屑導(dǎo)航、網(wǎng)站排名、營(yíng)銷型網(wǎng)站建設(shè)、用戶體驗(yàn)、網(wǎng)站收錄、網(wǎng)站制作
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請(qǐng)盡快告知,我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如需處理請(qǐng)聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時(shí)需注明來源: 創(chuàng)新互聯(lián)