MySQL常用命令

> 统计MySQL数据库表大小

计算MySQL表的字节大小

use information_schema;

select data_length,index_length from information_schema.tables where table_schema='dbtest' and table_name = 'tb_test';


+-------------+--------------+
| data_length | index_length |
+-------------+--------------+
|  1172307968 |            0 |
+-------------+--------------+
1 row in set (0.01 sec) 



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 information_schema.tables where table_schema='dbtest' and table_name = 'tb_test';  


+----------------+-----------------+
| data_length_MB | index_length_MB |
+----------------+-----------------+
| 1118.00MB      | 0.00MB          |
+----------------+-----------------+

select table_name, concat(round(sum((data_length+index_length)/1024/1024),2),'mb')  as sizeofmb, table_rows 
  from information_schema.tables 
  where table_schema='databasename' 
  and table_name='tablename';

> MySQL数据库表的空间回收

delete删除以后并不会立即回收，查看表大小占用仍然不变，可以执行alter table操作实现空间回收

 alter table dxt_steps_record engine=innodb; 

> 表复制(方式创建表)和数据复制，基于已有的表复制

方式1）先拷贝表结构（复制表结构时不会复制数据）、再添加数据【推荐该方式】

DROP TABLE IF EXISTS table01_new;

CREATE TABLE table01_new LIKE table01;

INSERT INTO table01_new SELECT * FROM table01;

如果要跨库拷贝，则表名使用database.table的方式，例如：

DROP TABLE IF EXISTS db02.table01_new;

CREATA TABLE db02.table01_new LIKE db01.table01;

INSERT INTO db02.table01_new SELECT * FROM db01.table01;

如果想要拷贝一部分数据、可以加WHERE条件，例如：INSERT INTO db02.table01_new SELECT * FROM db01.table01 WHERE id < 100;

注意：该方式复制表结构时会包含 pk/uk/index/auto_increament 等属性的复制。

方式2）同时拷贝表结构和数据【不推荐】

DROP TABLE IF EXISTS table01_new;

CREATE TABLE table01_new SELECT * FROM table01;

-- 如果要跨库拷贝: CREATE TABLE db02.table01_new SELECT * FROM db01.table01;

-- 该方式也可以通过加WHERE条件实现仅拷贝表结构而不拷贝数据的效果：CREATE TABLE db02.table01_new SELECT * FROM db01.table01 WHERE false;

注意：该方式复制表结构和数据，但不包含 pk/uk/index/auto_increament 等属性的复制。

pk/uk/index/auto_increament 等属性需要自己手动修改表进行单独修改添加。

测试示例：

DROP TABLE IF EXISTS test.tbtest01;

DROP TABLE dbutf8.tbtest01_new1;

DROP TABLE dbutf8.tbtest01_new2;

CREATE TABLE test.tbtest01 (

id integer not null,

uname varchar(100),

info varchar(100),

cnt integer,

PRIMARY KEY pk_id (id),

UNIQUE KEY uname_uk (uname),

KEY info_idx (info)

);

INSERT INTO test.tbtest01(id,uname,info,cnt) values (1,'a','ss',100),(2,'b','uu',150);

SELECT * FROM test.tbtest01;

SHOW CREATE TABLE test.tbtest01;

/*

CREATE TABLE `tbtest01` (

`id` int(11) NOT NULL,

`uname` varchar(100) DEFAULT NULL,

`info` varchar(100) DEFAULT NULL,

`cnt` int(11) DEFAULT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `uname_uk` (`uname`),

KEY `info_idx` (`info`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

*/

-- 方式1

CREATE TABLE dbutf8.tbtest01_new1 LIKE test.tbtest01;

INSERT INTO dbutf8.tbtest01_new1 SELECT * FROM test.tbtest01;

SHOW CREATE TABLE dbutf8.tbtest01_new1;

/*

CREATE TABLE `tbtest01_new1` (

`id` int(11) NOT NULL,

`uname` varchar(100) DEFAULT NULL,

`info` varchar(100) DEFAULT NULL,

`cnt` int(11) DEFAULT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `uname_uk` (`uname`),

KEY `info_idx` (`info`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

*/

SELECT * FROM dbutf8.tbtest01_new1;

-- 方式2:

CREATE TABLE dbutf8.tbtest01_new2 SELECT * FROM test.tbtest01;

SHOW CREATE TABLE dbutf8.tbtest01_new2;

/*

CREATE TABLE `tbtest01_new2` (

`id` int(11) NOT NULL,

`uname` varchar(100) CHARACTER SET utf8 DEFAULT NULL,

`info` varchar(100) CHARACTER SET utf8 DEFAULT NULL,

`cnt` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

*/

SELECT * FROM dbutf8.tbtest01_new2;