{"id":290,"date":"2014-06-30T14:36:15","date_gmt":"2014-06-30T14:36:15","guid":{"rendered":"http:\/\/ixyzero.com\/blog\/?p=290"},"modified":"2014-06-30T14:36:15","modified_gmt":"2014-06-30T14:36:15","slug":"%e6%80%8e%e6%a0%b7%e7%94%a8%e5%91%bd%e4%bb%a4%e6%9f%a5%e7%9c%8bmysql%e6%95%b0%e6%8d%ae%e5%ba%93%e5%a4%a7%e5%b0%8f%ef%bc%9f","status":"publish","type":"post","link":"https:\/\/ixyzero.com\/blog\/archives\/290.html","title":{"rendered":"\u600e\u6837\u7528\u547d\u4ee4\u67e5\u770bMysql\u6570\u636e\u5e93\u5927\u5c0f\uff1f"},"content":{"rendered":"<h4>\u600e\u6837\u7528\u547d\u4ee4\u67e5\u770bMysql\u6570\u636e\u5e93\u5927\u5c0f\uff1f<\/h4>\n<p><code>mysql&gt; use\u00a0information_schema;<\/code><\/p>\n<p><code>mysql&gt; select TABLE_SCHEMA,TABLE_NAME,DATA_LENGTH from tables;<\/code> \u00a0#<span style=\"color: #0000ff;\">\u901a\u8fc7\u5728information_schema\u6570\u636e\u5e93\u7684tables\u8868\u4e2d\u7684\u6570\u636e\uff0c\u53ef\u4ee5\u67e5\u770b\u6bcf\u4e2a\u6570\u636e\u5e93\u7684\u5404\u4e2a\u8868\u6240\u5360\u7a7a\u95f4\u5927\u5c0f<\/span>\uff08<span style=\"color: #ff0000;\"><strong>DATA_LENGTH\u662f\u4ee5Byte\u4e3a\u5355\u4f4d<\/strong><\/span>\uff09<\/p>\n<h6>1\u3001\u4f7f\u7528 schema \u6570\u636e\u5e93\uff08\u5b58\u653e\u4e86\u5176\u4ed6\u7684\u6570\u636e\u5e93\u7684\u4fe1\u606f\uff09<\/h6>\n<p><code>use information_schema;<\/code><\/p>\n<h6>2\u3001\u67e5\u8be2\u6240\u6709\u6570\u636e\u7684\u5927\u5c0f<\/h6>\n<p><code>select concat(round(sum(DATA_LENGTH\/1024\/1024),2),'MB') as data from TABLES;<\/code><\/p>\n<h6>3\u3001\u67e5\u770b\u6307\u5b9a\u6570\u636e\u5e93\u7684\u5927\u5c0f<\/h6>\n<p>\u6bd4\u5982\u8bf4\u6570\u636e\u5e93test<\/p>\n<p><code>select concat(round(sum(DATA_LENGTH\/1024\/1024),2),'MB') as data from TABLES where table_schema='test';<\/code><\/p>\n<h6>4\u3001\u67e5\u770b\u6307\u5b9a\u6570\u636e\u5e93\u7684\u8868\u7684\u5927\u5c0f<\/h6>\n<p>\u6bd4\u5982\u8bf4\u6570\u636e\u5e93test\u4e2d\u7684test_user\u8868<\/p>\n<p><code>select concat(round(sum(DATA_LENGTH\/1024\/1024),2),'MB') as data from TABLES where table_schema='test' and table_name='test_user';<\/code><\/p>\n<p>==<\/p>\n<h6>MySQL\u6570\u636e\u5e93\u4e2d\u7684information_schema\u5e93\u7684tables\u8868\u7684\u7ed3\u6784\uff1a<\/h6>\n<pre class=\"lang:default decode:true \">mysql&gt; desc tables;\n+-----------------+---------------------+------+-----+---------+-------+\n| Field | Type | Null | Key | Default | Extra |\n+-----------------+---------------------+------+-----+---------+-------+\n| TABLE_CATALOG | varchar(512) | NO | | | |\n| TABLE_SCHEMA | varchar(64) | NO | | | |\n| TABLE_NAME | varchar(64) | NO | | | |\n| TABLE_TYPE | varchar(64) | NO | | | |\n| ENGINE | varchar(64) | YES | | NULL | |\n| VERSION | bigint(21) unsigned | YES | | NULL | |\n| ROW_FORMAT | varchar(10) | YES | | NULL | |\n| TABLE_ROWS | bigint(21) unsigned | YES | | NULL | |\n| AVG_ROW_LENGTH | bigint(21) unsigned | YES | | NULL | |\n| DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |\n| MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |\n| INDEX_LENGTH | bigint(21) unsigned | YES | | NULL | |\n| DATA_FREE | bigint(21) unsigned | YES | | NULL | |\n| AUTO_INCREMENT | bigint(21) unsigned | YES | | NULL | |\n| CREATE_TIME | datetime | YES | | NULL | |\n| UPDATE_TIME | datetime | YES | | NULL | |\n| CHECK_TIME | datetime | YES | | NULL | |\n| TABLE_COLLATION | varchar(32) | YES | | NULL | |\n| CHECKSUM | bigint(21) unsigned | YES | | NULL | |\n| CREATE_OPTIONS | varchar(255) | YES | | NULL | |\n| TABLE_COMMENT | varchar(2048) | NO | | | |\n+-----------------+---------------------+------+-----+---------+-------+\n21 rows in set (0.00 sec)\n\n\u5176\u4e2d\uff1a\nTABLE_SCHEMA \u662f\u6570\u636e\u5e93\u540d\nTABLE_NAME \u662f\u6570\u636e\u5e93\u4e2d\u7684\u8868\u540d\nDATA_LENGTH \u8868\u7684\u5927\u5c0f\nINDEX_LENGTH \u7d22\u5f15\u5927\u5c0f<\/pre>\n<p>==<\/p>\n<h6>\u53c2\u8003\u7f51\u5740\uff1a<\/h6>\n<p><a href=\"http:\/\/www.apoyl.com\/?p=458\" target=\"_blank\">http:\/\/www.apoyl.com\/?p=458<\/a><\/p>\n<h6>\u66f4\u591aMySQL\u547d\u4ee4\u5c0f\u7ed3\uff1a<\/h6>\n<pre class=\"lang:default decode:true \">use information_schema;\nselect concat(round(sum(DATA_LENGTH\/1024\/1024), 2), 'MB') as data from TABLES;\n\nselect concat(round(sum(DATA_LENGTH\/1024\/1024), 2), 'MB') as data from TABLES where table_schema= 'apoyl' ;\n\nselect concat(round(sum(DATA_LENGTH\/1024\/1024), 2), 'MB') as data from TABLES where table_schema= 'apoyl' and table_name= 'apoyl_test' ;\n\nshow databases;\nshow tables;\n\nshow processlist;\n\nshow variables like \"plugin%\";\n\nselect @@basedir;\n\/*\nsystem_user()   \u7cfb\u7edf\u7528\u6237\u540d\nuser()          \u7528\u6237\u540d\ncurrent_user    \u5f53\u524d\u7528\u6237\u540d\nsession_user()  \u8fde\u63a5\u6570\u636e\u5e93\u7684\u7528\u6237\u540d\ndatabase()      \u6570\u636e\u5e93\u540d\nversion()       MYSQL\u6570\u636e\u5e93\u7248\u672c\nload_file()     MYSQL\u8bfb\u53d6\u672c\u5730\u6587\u4ef6\u7684\u51fd\u6570\n@@datadir       \u8bfb\u53d6\u6570\u636e\u5e93\u8def\u5f84\n@@basedir       MYSQL \u5b89\u88c5\u8def\u5f84\n@@version_compile_os    \u64cd\u4f5c\u7cfb\u7edf\n*\/\nselect Host,User,Password from mysql.user;\nselect * from mysql.func;\n\nshow variables like '%skip_networking%';\nshow variables like 'collation_%';\nshow variables like 'char_%';\n\nuse db_name;\nstatus;         #\u67e5\u770b\u67d0\u4e2a\u6570\u636e\u5e93\u7684\u7f16\u7801\n\nshow create database db_name;   #\u67e5\u770b\u67d0\u4e2a\u6570\u636e\u5e93\u7684\u7f16\u7801\nshow [global] status;\nshow variables;\nshow engines;\nshow grants;    #\u67e5\u770b\u6743\u9650\u5206\u914d\u60c5\u51b5\nshow columns from db_name.tb_name;  &lt;=&gt; desc db_name.tb_name\nshow create table db_name.tb_name;\n\nmysql&gt; show tables from information_schema;\nmysql&gt; show index from information_schema.USER_PRIVILEGES;\nmysql&gt; select * from information_schema.user_privileges;<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>\u600e\u6837\u7528\u547d\u4ee4\u67e5\u770bMysql\u6570\u636e\u5e93\u5927\u5c0f\uff1f mysql&gt; use\u00a0information_schema; my [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14,12],"tags":[139,16],"class_list":["post-290","post","type-post","status-publish","format-standard","hentry","category-database","category-tools","tag-information_schema","tag-mysql"],"views":3386,"_links":{"self":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/290","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/comments?post=290"}],"version-history":[{"count":0,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/290\/revisions"}],"wp:attachment":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/media?parent=290"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/categories?post=290"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/tags?post=290"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}