{"id":1394,"date":"2024-05-08T10:07:57","date_gmt":"2024-05-08T02:07:57","guid":{"rendered":"http:\/\/oneai.eu.org\/?p=1394"},"modified":"2024-05-08T10:07:57","modified_gmt":"2024-05-08T02:07:57","slug":"mysql-%e5%b8%b8%e7%94%a8%e6%9f%a5%e8%af%a2%e8%af%ad%e5%8f%a5","status":"publish","type":"post","link":"https:\/\/oneai.eu.org\/?p=1394","title":{"rendered":"mysql \u5e38\u7528\u67e5\u8be2\u8bed\u53e5"},"content":{"rendered":"

1.show table status from dbname\\G; \/\/\u67e5\u8be2\u6570\u636e\u5e93\u7684\u788e\u7247
\nuse test;
\nmysql> show table status from test\\G;
\n 1. row <\/em><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong>
\nName: test
\nEngine: InnoDB
\nVersion: 10
\nRow_format: Compact
\nRows: 45
\nAvg_row_length: 364
\nData_length: 16384
\nMax_data_length: 0
\nIndex_length: 16384
\nData_free: 0
\nAuto_increment: NULL
\nCreate_time: 2015-01-14 11:25:23
\nUpdate_time: NULL
\nCheck_time: NULL
\nCollation: latin1_swedish_ci
\nChecksum: NULL
\nCreate_options:
\nComment:
\n1 row in set (0.00 sec)
\nData_free\uff1a\u4e0d\u7b490\u8868\u793a\u6709\u788e\u7247<\/h2>\n

\u67e5\u8be2\u6240\u6709\u5e93\uff08\u9664mysql\u5e93\uff09\u7684\u6240\u6709\u788e\u7247\u4fe1\u606f
\nselect table_schema, table_name, data_free, engine from information_schema.tables where table_schema not in ('information_schema', 'mysql') and data_free > 0; <\/p>\n

2.show create table from tablename \/\/\u67e5\u770b\u5efa\u8868\u8bed\u53e5
\n3.show create function from functionname \/\/\u67e5\u770b\u521b\u5efa\u51fd\u6570\u8bed\u53e5
\n4 show indexes from tablename \/\/\u67e5\u770b\u8868\u7684\u7d22\u5f15
\n5 show variable like 'varname' \/\/\u67e5\u770b\u914d\u7f6e\u53d8\u91cf\u7684\u503c
\n6 show [full ] processlist \/\/\u67e5\u770bSQL\u8fdb\u7a0b\u5217\u8868
\n7 show [engine innodb] status \\G; \/\/\u67e5\u770b[innodb]\u5404\u79cd\u72b6\u6001<\/p>\n

    \n
  1. show databases \/show tables \/desc tablename <\/li>\n
  2. \n

    show plugins \/\/\u663e\u793a\u6240\u6709\u63d2\u4ef6
    \n10\u3002show table status from [dbname] where name='tablename'; \/\/\u67e5\u770b\u67d0\u5e93\u67d0\u4e2a\u8868\u7684\u72b6\u6001\u4fe1\u606f
    \nshow slave\/master status; \/\/\u67e5\u770b\u4e3b\u4ece\u5e93\u7684\u72b6\u6001
    \nSHOW MASTER LOGS \/\/\u67e5\u770bbinlog \u65e5\u5fd7
    \nSHOW BINARY LOGS \/\/\u67e5\u770bbinlog\u65e5\u5fd7
    \nSHOW SLAVE HOSTS \/\/\u67e5\u770b\u4ece\u670d\u52a1\u5668\u4fe1\u606f
    \nshow binlog events\\G \/\/\u67e5\u770bbinlog\u4e8b\u4ef6\u3002<\/p>\n

    CREATE DATABASE cptest<\/code> DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;<\/p>\n<\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"

    1.show table status from dbname\\G; \/\/\u67e5\u8be2\u6570\u636e\u5e93\u7684\u788e\u7247 use test; mysql> show table status from test\\G; 1. row Name: test Engine: InnoDB Version: 10 Row_format: Compact Rows: 45 Avg_row_length: 364 Data_length: 16384 Max_data_length: 0 Index_length: 16384 Data_free: 0 Auto_increment: NULL Create_time: 2015-01-14 11:25:23 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) Data_free\uff1a\u4e0d\u7b490\u8868\u793a\u6709\u788e\u7247 \u67e5\u8be2\u6240\u6709\u5e93\uff08\u9664mysql\u5e93\uff09\u7684\u6240\u6709\u788e\u7247\u4fe1\u606f select table_schema, table_name, data_free, engine from information_schema.tables where table_schema not in (‘information_schema’, ‘mysql’) and data_free > 0; 2.show create table from tablename \/\/\u67e5\u770b\u5efa\u8868\u8bed\u53e5 3.show create function from functionname \/\/\u67e5\u770b\u521b\u5efa\u51fd\u6570\u8bed\u53e5 4 show indexes from tablename \/\/\u67e5\u770b\u8868\u7684\u7d22\u5f15 5 show variable like ‘varname’ \/\/\u67e5\u770b\u914d\u7f6e\u53d8\u91cf\u7684\u503c 6 show [full ] processlist \/\/\u67e5\u770bSQL\u8fdb\u7a0b\u5217\u8868 7 show [engine innodb] status \\G; \/\/\u67e5\u770b[innodb]\u5404\u79cd\u72b6\u6001 show databases \/show tables \/desc tablename show plugins \/\/\u663e\u793a\u6240\u6709\u63d2\u4ef6 10\u3002show table status from [dbname] where name=’tablename’; \/\/\u67e5\u770b\u67d0\u5e93\u67d0\u4e2a\u8868\u7684\u72b6\u6001\u4fe1\u606f show slave\/master status; \/\/\u67e5\u770b\u4e3b\u4ece\u5e93\u7684\u72b6\u6001 SHOW MASTER LOGS \/\/\u67e5\u770bbinlog \u65e5\u5fd7 SHOW BINARY LOGS \/\/\u67e5\u770bbinlog\u65e5\u5fd7 SHOW SLAVE HOSTS \/\/\u67e5\u770b\u4ece\u670d\u52a1\u5668\u4fe1\u606f show binlog events\\G \/\/\u67e5\u770bbinlog\u4e8b\u4ef6\u3002 CREATE DATABASE cptest DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_eb_attr":"","footnotes":""},"categories":[83],"tags":[],"class_list":["post-1394","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/oneai.eu.org\/index.php?rest_route=\/wp\/v2\/posts\/1394","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/oneai.eu.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/oneai.eu.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/oneai.eu.org\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/oneai.eu.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1394"}],"version-history":[{"count":1,"href":"https:\/\/oneai.eu.org\/index.php?rest_route=\/wp\/v2\/posts\/1394\/revisions"}],"predecessor-version":[{"id":1395,"href":"https:\/\/oneai.eu.org\/index.php?rest_route=\/wp\/v2\/posts\/1394\/revisions\/1395"}],"wp:attachment":[{"href":"https:\/\/oneai.eu.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1394"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/oneai.eu.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1394"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/oneai.eu.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1394"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}