mysql命令记录
链接:
MySQL Online DDL: https://www.cnblogs.com/mysql-dba/p/6192897.html
外键约束
禁用外键约束:
SET FOREIGN_KEY_CHECKS=0
启动外键约束:
SET FOREIGN_KEY_CHECKS=1;
查看当前FOREIGN_KEY_CHECKS的值可用如下命令:
SELECT @@FOREIGN_KEY_CHECKS;
外键
添加外键
ALTER TABLE `wenshu_data`.`lawyersintelprop` ADD CONSTRAINT `fk_lawyersintelprop__wenshu_id` FOREIGN KEY (`wenshu_id`) REFERENCES `wenshu_data`.`basicintelprop` (`wenshu_id`);
删除外键
ALTER TABLE `wenshu_data`.`lawyerscomsecinsnot` DROP FOREIGN KEY `fk_lawyerscomsecinsnot__wenshu_id`;
自增id
主键自增id
MySQL [data_third_part]> alter table wenshu_info005 add id int;
Query OK, 0 rows affected (6 min 35.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
MySQL [data_third_part]> alter table wenshu_info005 drop primary key;
Query OK, 129945 rows affected (3 min 18.66 sec)
Records: 129945 Duplicates: 0 Warnings: 0
MySQL [data_third_part]> alter table wenshu_info005 change id id int not null auto_increment primary key;
Query OK, 129945 rows affected (3 min 19.04 sec)
Records: 129945 Duplicates: 0 Warnings: 0
非主键自增id
alter table basicintelprop add id int auto_increment Unique;
索引:
普通增加索引
索引的通常命名: idx_<表名字>__<添加索引字段1>__<添加索引字段2>
CREATE INDEX `idx_cnipsundata__agent` ON `cnipsundata` (`agent`)
删除索引
ALTER TABLE `wenshu_data`.`lawyerinfo` DROP INDEX `idx_lawyerinfo__name`;
修改复合索引
ALTER TABLE `wenshu_data`.`lawyerinfo` DROP INDEX `idx_lawyerinfo__name__lawyer_firm__region`, ADD INDEX `idx_lawyerinfo__name__lawyer_firm__region1` USING BTREE (`name`, `lawyer_firm`, `region`) comment '';
大表结构修改
添加字段:
1600万数据(51GB存储),表结构较大,添加字段, 用3小时40分钟:
mysql> select id from cnipsundata order by id desc limit 1;
+----------+
| id |
+----------+
| 16709353 |
+----------+
1 row in set (0.05 sec)
mysql> alter table cnipsundata add province varchar(64);
Query OK, 0 rows affected (3 hours 43 min 10.59 sec)
Records: 0 Duplicates: 0 Warnings: 0
查看MySQL数据库大小
查看所有数据库大小
select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from INFORMATION_SCHEMA.TABLES;
查看指定数据库大小
select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from INFORMATION_SCHEMA.TABLES where table_schema='CarData';
查看指定数据库的指定表的大小
select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from INFORMATION_SCHEMA.TABLES where table_schema='CarData' and table_name='driver020294';
查看指定数据库指定表的其他大小
select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data_size,
-> concat(round(sum(MAX_DATA_LENGTH/1024/1024),2),'MB') as max_data_size,
-> concat(round(sum(INDEX_LENGTH/1024/1024),2),'MB') as index_size,
-> concat(round(sum(DATA_FREE/1024/1024),2),'MB') as data_free
-> from INFORMATION_SCHEMA.TABLES where table_schema='CarData' and table_name='driver020294';
插入更新语句ON DUPLICATE KEY UPDATE
- if-else写法(效率太差,每次都需要执行两条SQL语句; 高并发的情况下数据会出问题,不能保证原子性):
if not exists (select node_name from node_status where node_name = target_name) insert into node_status(node_name,ip,...) values('target_name','ip',...) else update node_status set ip = 'ip',site = 'site',... where node_name = target_name
- ON DUPLICATE KEY UPDATE:
如果你插入的记录导致一个UNIQUE索引或者primary key(主键)出现重复,那么就会认为该条记录存在,则执行update语句而不是insert语句,反之,则执行insert语句而不是更新语句。
INSERT INTO tablename(field1,field2, field3, ...) VALUES(value1, value2, value3, ...) ON DUPLICATE KEY UPDATE field1=value1,field2=value2, field3=value3, ...;