mysql常用快速查詢修改操作

mysql常用快速查詢修改操作

一、查找并修改非innodb引擎為innodb引擎

# 通用操作
mysql> select  concat('alter table ',table_schema,'.',table_name,' engine=innodb;') from information_schema.tables where table_schema not in ('information_schema','mysql','performance_schema') and engine='myisam'; 

?

# 示例
select concat('alter table test.',table_name,' engine=innodb;') from information_schema.tables where table_schema not in ('information_schema','mysql','performance_schema') and engine='myisam';
mysql> select concat('alter table test.',table_name,' engine=innodb;') from information_schema.tables where table_schema not in ('information_schema','mysql','performance_schema') and engine='myisam'; 
+----------------------------------------------------------+
| concat('alter table test.',table_name,' engine=innodb;') |
+----------------------------------------------------------+
| alter table test.tempusermap engine=innodb;????????????? |
| alter table test.users_relation_list engine=innodb;????? |
+----------------------------------------------------------+
2 rows in set (0.58 sec)

mysql> 

二、查詢數據大小

在需要備份數據庫里面的數據時，我們需要知道數據庫占用了多少磁盤大小，可以通過一些sql語句查詢到整個數據庫的容量，也可以單獨查看表所占容量。

1、要查詢表所占的容量，就是把表的數據和索引加起來就可以了

mysql>  select TABLE_SCHEMA,(sum(DATA_LENGTH)+sum(INDEX_LENGTH))/1024/1024/1024 AS 'size_g' from information_schema.tables where TABLE_SCHEMA NOT IN ('information_schema','mysql','performance_schema')  group by TABLE_SCHEMA;
+--------------+-----------------+
| TABLE_SCHEMA | size_g          |
+--------------+-----------------+
| db01         | 13.810716629028 |
| db02         |  0.279693603516 |
| test         |  0.143768310547 |
+--------------+-----------------+
3 rows in set (9.06 sec)

2、查詢所有的數據大小

select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') from information_schema.tables; -- 查詢所有的數據大小
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') from information_schema.tables; -- 查詢所有的數據大小
+-------------------------------------------------+
| concat(round(sum(DATA_LENGTH/1024/1024),2),'M') |
+-------------------------------------------------+
| 5324.54M                                        |
+-------------------------------------------------+
1 row in set (8.60 sec)

3、查詢某個表的數據

select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') from tables where table_schema='test' AND table_name='USER';mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') from information_schema.tables where table_schema='test' AND table_name='USER';
+-------------------------------------------------+
| concat(round(sum(DATA_LENGTH/1024/1024),2),'M') |
+-------------------------------------------------+
| 73.61M                                          |
+-------------------------------------------------+
1 row in set (0.03 sec)

mysql>select table_name,concat(round(sum(DATA_LENGTH/1024/1024),2),'M') AS 'SIZE_MB' from information_schema.tables where table_schema='db222' group by table_name order by SIZE_MB asc,table_name asc;
+----------------+---------+
| table_name???? | SIZE_MB |
+----------------+---------+
| t1???????????? | 0.02M?? |
| t_user_partion | 487.33M |
| t_user_id3???? | 71.61M? |
| t_user_id0???? | 74.61M? |
| t_user_id1???? | 74.61M? |
| t_user_id2???? | 74.61M? |
| t_user_id4???? | 74.61M? |
| t_user_id5???? | 74.61M? |
| t_user_id6???? | 74.61M? |
| t_user_id7???? | 74.61M? |
| t_user_id8???? | 74.61M? |
| t_user_id9???? | 74.61M? |
+----------------+---------+
12 rows in set (0.00 sec)

mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') AS table_size,table_name from information_schema.tables where table_schema='fudao_account' AND table_name IN ('db3','db2','db3','db1') group by table_name;
+------------+------------------------+
| table_size | table_name???????????? |
+------------+------------------------+
| 2.52M????? | db1?????????? ??? ??? ?? |
| 0.23M????? | db2??????????????????? |
| 0.30M????? | db3??????????????????? |
+------------+------------------------+
3 rows in set (0.00 sec)

mysql> 

?