MySQL数据库基础操作总结

by 沧海之树 In 工作

连接上数据库以后执行如下命令

判断当前数据库文件存储在哪个目录：

show variables like '%dir%';
mysql> show variables like '%dir%';
+-----------------------------------------+----------------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------------+
| basedir | /usr/ |
| binlog_direct_non_transactional_updates | OFF |
| character_sets_dir | /usr/share/mysql/charsets/ |
| datadir | /var/lib/mysql/ |
| innodb_data_home_dir | |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 90 |
| plugin_dir | /usr/lib64/mysql/plugin |
| slave_load_tmpdir | /tmp |
| tmpdir | /tmp |
+-----------------------------------------+----------------------------+
10 rows in set (0.00 sec)

数据库文件的大小以及排序：
du -s /var/lib/mysql/xxx/* | sort -n

124 /var/lib/mysql/xxx/xxx_ecms_news_data_1.MYI
136 /var/lib/mysql/xxx/xxx_ecms_news_index.MYD
172 /var/lib/mysql/xxx/xxx_enewsf.MYD
176 /var/lib/mysql/xxx/xxx_enewstempbak.MYD
208 /var/lib/mysql/xxx/xxx_enewsfile_1.MYI
240 /var/lib/mysql/xxx/xxx_ecms_news_data_1.MYD
252 /var/lib/mysql/xxx/xxx_enewstagsdata.MYI
296 /var/lib/mysql/xxx/xxx_enewsdolog.MYI
404 /var/lib/mysql/xxx/xxx_ecms_news.MYI
484 /var/lib/mysql/xxx/xxx_ecms_news_index.MYI
956 /var/lib/mysql/xxx/xxx_enewsdolog.MYD
1820 /var/lib/mysql/xxx/xxx_enewsfile_1.MYD
3264 /var/lib/mysql/xxx/xxx_ecms_news.MYD
可见数据库文件都是比较小的，因为数据库文件里面只保存了极小的数据，大部分文件都是存储在指定的路
径下，只有极少的文字，根本也只能占据很小的空间；
查看表占用空间大小

use information_schema;

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select concat(round(sum(data_length/1024/1024),2),'MB') as data_length_MB,concat(round
(sum(index_length/1024/1024),2),'MB') as index_length_MB from tables where
table_schema='xxx' and table_name = 'xxx_ecms_news';
+----------------+-----------------+
| data_length_MB | index_length_MB |
+----------------+-----------------+
| 3.18MB | 0.39MB |
+----------------+-----------------+
1 row in set (0.03 sec)

mysql> select concat(round(sum(data_length/1024/1024),2),'MB') as data_length_MB,concat(round
(sum(index_length/1024/1024),2),'MB') as index_length_MB from tables where
table_schema='xxx' and table_name = 'xxx_ecms_news_data_1';
+----------------+-----------------+
| data_length_MB | index_length_MB |
+----------------+-----------------+
| 0.23MB | 0.12MB |
+----------------+-----------------+
1 row in set (0.00 sec)