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

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

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 ='表名';

查看字段的完整信息

1	show full columns from 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密码

> /usr/bin/mysqladmin -u root password '123456'
或
mysql> UPDATE user SET password=PASSWORD('123456') WHERE user='root';`
mysql> FLUSH PRIVILEGES;`

mysqldump备份报-bash: c89#64: command not found之解决
因为密码中包含特殊字符，已知的特殊字符有!&两个。处理这种情况也很简单，以下是两种办法：
1.对特殊字符进行转义，如123!\
2.将整个密码用单引号引起来，如’123!’
从使用易用性上讲，还是后者更简单一些，因为我无需关心哪些是特殊字符，整一个引起来就完了。
例mysqldump -h127.0.0.1 -P3306 -uroot -p'123456!&' database_name | gzip > /usr/backup/database_name.sql.gz

查看mysql版本

# 没有连接到服务器
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数据库导入数据的速度：

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. 手动使用事物；

split函数，mysql默认没有split函数，但是可以换个方式来实现

1
2
3

mysql> select SUBSTRING_INDEX('name=demo&user=xxx','&', 1)

mysql> select SUBSTRING_INDEX('name=demo&user=xxx','&', -1)

优化

[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图解