MySQL日常命令

从文件中加载数据到数据库,类似hive的load data

1
2
3
load data infile '/tmp/t0.txt' ignore into table t0 character set gbk
fields terminated by ',' enclosed by '"'
lines terminated by '\n' (`name`,`age`,`description`);

Linux上安装MySQL时出现不兼容的解决办法 http://www.linuxidc.com/Linux/2012-06/62156.htm

使用新表的数据覆盖原有表的数据:

1
2
3
4
5
6
7
truncate table table_name;
insert into table_name select * from table_name_old;

# 或者

delete from table_name;
insert into table_name select * from table_name_old;

查看创建表的sql语句 show create table tablename;

查看表的最后更新时间(主要针对没有更新时间字段的表)

1
SELECT * FROM information_schema.TABLES WHERE TABLE_NAME ='表名';

给mysql用户附权限,不用先创建用户,对于已经有的用户,如果附权限会没用,这时就要drop掉原来的用户

1
2
drop user wangjun@'%';
GRANT ALL PRIVILEGES ON `wangjun`.* TO 'wangjun'@'%' IDENTIFIED BY 'wangjun#123456' WITH GRANT OPTION;

MySQL InnoDB默认Row-Level Lock,所以只有「明确」地指定主键,MySQL 才会执行Row lock (只锁住被选取的数据) ,否则MySQL 将会执行Table Lock (将整个数据表单给锁住)。如果主键是范围或条件不是id=1 都是表锁。参考:http://www.36nu.com/post/162.html

如果使用union时不需要过滤重复数据就使用UNION ALL

mysql更新关联表

1
2
3
UPDATE table_name a
INNER JOIN table_name b ON a.column_name = b.column_name
SET a.column_name = CONCAT(b.column_name,'_',a.id)

创建新表备份当前表

1
create table fund_ua_bak_20170701 as select * from fund_ua_bak

设置主键,有些历史表没有主键

1
alter table table_name change id id int primary key auto_increment;

mysql优化器支持以下两种内置的日期函数进行分区:

1
2
TO_DAYS() 和 YEAR()
原文链接:http://blog.csdn.net/zhang168/article/details/46911305

mysql 表解锁:进mysql命令行

1
2
3
show processlist;   (查看锁表带进程号)

kill {ID}

mysql修改表名是不耗时的,不管表的数据量有多大

1
ALTER  TABLE odl_table_name RENAME TO new_table_name

修改mysql密码

1
2
3
4
> /usr/bin/mysqladmin -u root password '123456'

mysql> UPDATE user SET password=PASSWORD('123456') WHERE user='root';`
mysql> FLUSH PRIVILEGES;`

查看mysql版本

1
2
3
4
5
6
7
8
9
# 没有连接到服务器
mysql -V

# 已经连接到服务器
> select version();
# 或
mysql> status;
# 或
mysql> \s;

生成随机字符串

1
select substring(MD5(RAND()),1,20)

模糊搜索表

1
show tables like '%campaign%';

查询分组的第一条完整记录(需要mysql支持full_group)

1
2
3
SELECT t.user_id,t.group_id,t.opt_type,t.code,t.broker_id ,sum(fee) AS fee,min(opt_date) AS start_date ,max(opt_date) AS end_date
FROM (SELECT * FROM invest ORDER BY opt_date ASC) t
GROUP BY group_id

mysql数据库导入数据的速度:

1
2
3
4
5
6
0. 最快的当然是直接 copy 数据库表的数据文件(版本和平台最好要相同或相似);
1. 设置 innodb_flush_log_at_trx_commit = 0 ,相对于 innodb_flush_log_at_trx_commit = 1 可以十分明显的提升导入速度;
2. 使用 load data local infile 提速明显;
3. 修改参数 bulk_insert_buffer_size, 调大批量插入的缓存;
4. 合并多条 insert 为一条: insert into t values(a,b,c), (d,e,f) ,,,
5. 手动使用事物;

优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
[mysql]
max_heap_table_size = 64M
tmp_table_size = 64M
binlog_cache_size = 4M
sort_buffer_size = 4M
join_buffer_size = 4M
key_buffer_size = 256M
vread_buffer_size = 4M
read_rnd_buffer_size = 16M
innodb_buffer_pool_size = 256M
thread_cache_size = 8
thread_concurrency = 4
max_allowed_packet = 16M
query_cache_limit = 4M
innodb_file_io_threads = 4
innodb_thread_concurrency = 4
innodb_flush_log_at_trx_commit = 2
innodb_log_files_in_group = 3

#table_cache = 500
#query_cache_size = 32M

参考链接:
https://www.linuxyw.com/a/shujuku/20130506/216.html
http://leeyin.iteye.com/blog/1459274

常见错误

  • 异常内容:Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Queries and reconnect.
    这是因为MySql运行在safe-updates模式下,该模式会导致非主键条件下无法执行update或者delete命令,执行命令SET SQL_SAFE_UPDATES = 0;修改下数据库模式

Mysql join图解

JouyPub wechat
欢迎订阅「K叔区块链」 - 专注于区块链技术学习