成人午夜视频全免费观看高清-秋霞福利视频一区二区三区-国产精品久久久久电影小说-亚洲不卡区三一区三区一区

mysql怎么變成大表 mysql 表 大小

如何將mysql 表名變成大寫

以我的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

MySQL按月自動(dòng)創(chuàng)建分區(qū)表(千萬(wàn)級(jí)大表優(yōu)化)

對(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è)事件

續(xù) MYSQL,如何OPTIMIZE TABLE幾十GB以上的大表?

不在數(shù)據(jù)庫(kù)全文索引

對(duì)我有用[0]丟個(gè)板磚[0]引用舉報(bào)管理TOPmumubangditu(木木邦迪兔)等

級(jí):

MySQL8數(shù)據(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)

成都做網(wǎng)站
涡阳县| 庆云县| 红原县| 碌曲县| 茌平县| 凤冈县| 镇安县| 绥德县| 育儿| 泾源县| 东台市| 新龙县| 桑植县| 郓城县| 甘南县| 抚顺县| 泾源县| 泉州市| 东城区| 万载县| 馆陶县| 龙门县| 穆棱市| 邛崃市| 阿尔山市| 察雅县| 稷山县| 上林县| 崇信县| 准格尔旗| 墨竹工卡县| 鲜城| 蚌埠市| 淅川县| 大冶市| 汕头市| 和平县| 兴仁县| 诸城市| 鹤岗市| 乐东|