mysql 常用操作

浏览数：26 / 时间：2015年06月12日

1. mysql insert update on duplicate

如果更新 key 冲突，那么按照指定的规则来更新数据，有时候这样可以方便不少。还有一个是连表更新数据，这样可以将 table_b 的状态同步到 table_a, 本质上和多表查询是类似的。

insert into myblog (id,title,ctime) values(123,‘hello‘,now())on duplicate key update title=values(title),ctime=values(ctime);
update table_a a , table_b b set a.shop_status = b.group_status where a.shop_id = b.shop_id;

2. 插入，从另一张表选择内容插入到新表

将 blog_bak表中的所有数据导入到myblog 中，表 blog 和 blog_bak 应该有同样的表结构

insert into myblog( blog,ctime) select * from blog_bak;

3. mysql 索引

给自己的表添加索引，可以给多个字段添加索引,有下面两种方式，对应的，删除索引的方法也列在了下面。

create index index_name on table_name (column_list);
alter table table_name add index index_name (column_list);
drop index index_name on table;
alter table table_name drop index index_name;

4. mysql show

有时候遇到了 warnnings，怎么查看呢，可以用下面的命令,查看全表的信息,可以查到自己表的注释信息是可以用最后一个命令full fields from。

show warnings;
show errors;
show tables like ‘%talname%‘;
show full fields from your_table;

5. mysql 数据导入和导出

truncate table会清空表中的数据，包括auto_increment的字段都会被重置。mysqldump 可以又这些选择，导出表，导出库，导出表结构，导出表中的数据。或者整体导出，下面都有对应的shell命令。

mysqldump -h localhost -ppasswd  -uroot -d database > dump.sql ;
#只导出数据库的结构
mysqldump -h localhost -ppasswd  -uroot  database  > dump.sql ;
#导出数据库的结构和所有的数据
mysqldump -h localhost -ppasswd  -uroot -d database tablename > dump.sql ;
#只导出表结构
mysqldump -h localhost -ppasswd  -uroot  database tablename > dump.sql ;
#导出表结构和表中的数据
mysql -u root -p yourpasswd -h localhost yourdb < dump.sql
#将dump.sql 导出入到你的数据库

6. mysql 几个简单的时间处理函数

请不要吧 day 写成 days ，month , hour 同理，group by 多个字段从 col_a -> col_b -> col_c 优先级依次降低。

select date_format(now(),"%Y-%m-%d %H:%i:%s") now;
select date_sub(now(), interval 10 day) as yesterday; 
select * from test_table where status = 1 order by col_a desc, col_b desc, col_c asc limit 100;

7. mysql 变量

可以通过 select 给变量赋值，对，你没有看错,这两种方式都可以给@a赋值。使用的时候记得用@啊，就像PHP里面的$一样，这个变量只在这个链接周期中有效。

set @a = 100;
set @a:=100;
select @a:=300;

8. mysql 存储过程

下面是一个简单的存储过程的例子，因为 mysql 默认的终止符是; 而这个正好是存储过程的语法，所以在编写存储过程之前，先将 delimiter 改成 $，在存储过程结束之后，应将 delimiter 改成默认的; 这样符合我们的习惯, 最后是调用这个存储过程call p()。具体代码如下：

delimiter $ 
create procedure p()
begin
select * from ttt;
end;
$
delimiter ;     
call p();

9. mysql 中文乱码，设置成utf8.

下面是我的 centos 6.5 上的mysql 的配置文件，mysql是通过yum安装的,貌似版本都是5.1的….我添加的内容是斜体部分，目的就是设置默认的字符集为utf8 ，如果有些部分没有，请自行添加，比如[client]没有的话，在我的配置文件中，我就是自己添加的，不知道那个版本的mysql配置为何那么奇葩，以至于刚打开都不太敢相信自己的眼睛。

[client]
default_character_set=utf8
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
character_set_server=utf8
init_connect=’SET NAMES utf8’
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
default-character-set=utf8

10. mysql 权限管理

权限管理的所有信息都存放在mysql这个数据库中。只有通过root用户进去才可以进行对应的操作。前面一个是创建一个用户，host 是表明这个用户可以从什么地方连接数据库，设置成本地比较安全，注意其中的password()函数。flush privileges是让修改生效。最后的grant是将一个数据库t_sweet 授权给这个用户。

use mysql;
insert into mysql.user(Host,User,Password) values("localhost","phplamp",password("1234"));
flush privileges
grant all privileges on t_sweet.* to sweet_b@localhost identified by ‘yourpassword‘