Category Archives: 数据库

Mysql插入表情字符问题处理

最近在处理微信数据时,出现如下的错误:

ERROR[12-02 11:11:57]cn.hl.basic.datapersist.DbBasicService.execSql(DbBasicService.java:632): java.sql.SQLException: Incorrect string value: '\xF0\x9F\x8D\xAC",...' for column 'user_info' at row 1

错误是在获取微信用户信息后,插入数据库时发生的,基本可以判断是微信用户名中有特殊字符,所以导致数据插入不了

在网上也找资源半天,说是编码问题,需要调整编码为utf8mb4,但看了数据库这个字段的编码,却就是utf8mb4的,又看了些文章,网上有说要改MySQL服务端的编码,也就是修改配置,然而服务器的配置不是那么好动的,现在运行的项目有很多。

是否是我连接的时候编码就指定错了,或是可以通过连接URL来指定呢?这样想着,查看了MySQL的连接URL,目前的如下:

jdbc:mysql://ipandport/dbname?useOldAliasMetadataBehavior=true&autoReconnect=true&failOverReadOnly=false&characterEncoding=utf8

不是说使用utf8mb4吗?这里用的utf8,会影响吗?是这个问题吗?带着问题,又查看了网上的资料,

明白了一个问题,这些乱码是微信中的表情符,这些表情符是占4个字节的,而utf8却三个字节的,如果在连接URL中指定characterEncoding=utf8的话,那么他写入数据时就按照3个字节写入了,那肯定写不进去的,有不指定写入字节,按照本身的字符的字节来写吗?

有!!!就是useUnicode参数,设置为useUnicode=true,那么在写入数据时,会根据数据本身的字节来写,好吧,明白了问题的原因,果断试下,果然不出所料,实践证明了预想的结论。

说明:useUnicode的作用:

当设置useUnicode=true时,数据在存入数据库时会根据数据库字段的编码进行转换后,再存储。所以数据库的编码设置为utf8mb4,那么存储肯定没啥问题了。

正确的配置如下:

jdbc:mysql://ipandport/dbname?useOldAliasMetadataBehavior=true&autoReconnect=true&failOverReadOnly=false&userUnicode=true

以下为再网上查询的一些资料,放在这里,以供参考:

http://ourmysql.com/archives/1402

http://www.jianshu.com/p/20740071d854

网上还说了另一种方案,就是在知道会有这样字符的字段时,统一对其进行转码,比如转成base64的存入到数据库,取出时再统一转码,但是对于之前数据库中已经存在历史大量的数据,操作是很麻烦的,还要去刷数据,所以在连接指定字符是最简单可行的方法。

另外说下,对于有这样的字符,插入到数据库中的时候会变成?,不用担心,取出来后数据还是可以还原的。

7e5771da-804a-49c1-948d-42c83ad8a9aa

9a84e520-7b2b-468b-85e3-24e14962146a

关于join查询使用遇到的问题

今天发现服务器上的一个sql执行非常慢,两张关联表查询

有一张关注表user_focus,和一张关注备注表user_focus_note

sql如下:

select t1.FLYID,t1.FLYKEY,t1.ISPUSH,t1.NOTIFYSTATE,t2.description,t2.remind_times from USER_FOCUS t1 left join USER_FOCUS_note t2 on t1.ID=t2.user_focus_id where (t1.USERID=’1378416183′ or t1.PHONEID=’18820063′) and t1.ORDERTYPE=’0′ and t1.NOTIFYSTATE=’0′ and t1.UPDATETIME >= ’2016-09-24 00:00:00′ UNION select t3.FLYID,t3.FLYKEY,t3.ISPUSH,t3.NOTIFYSTATE,t4.description,t4.remind_times from USER_FOCUS t3 left join USER_FOCUS_note t4 on t3.ID=t4.user_focus_id where (t3.USERID=’1378416183′ or t3.PHONEID=’18820063′) and t3.ORDERTYPE=’0′ and t3.NOTIFYSTATE in (1,2) and t3.UPDATETIME >= ’2016-10-24 09:55:19′;

进一步分析,发现下面sql执行很慢,基本上再20s以上,但返回结果只有1000多条

select t1.FLYID,t1.FLYKEY,t1.ISPUSH,t1.NOTIFYSTATE,t2.description,t2.remind_times from USER_FOCUS t1 left join USER_FOCUS_NOTE t2 on t1.ID=t2.user_focus_id where (t1.USERID=’1378416183′ or t1.PHONEID=’18820063′) and t1.ORDERTYPE=’0′ and t1.NOTIFYSTATE=’0′ and t1.UPDATETIME >= ’2016-09-24 00:00:00′

其中user_focus有1600万条数据,PHONEID,USERID,UPDATETIME 都建了索引,

去掉关联查询后,速度一下子就提升上来了,基本100ms就执行完了,所以确定是使用left join的问题,查看了下user_focus_note表,发现有18w条数据,数据量不大啊,怎么回事?

仔细查看了sql,这里有用到t1.ID=t2.user_focus_id,USER_FOCUS_NOTE 表user_focus_id建立了一个索引,重新执行sql,速度果然很快。USER_FOCUS 表虽然只有1000多条数据满足,但是查询USER_FOCUS_NOTE表时都需要扫描全表,这样就有180000*1000条扫描,所以查询肯定很慢

总结:类似两个大表,或一个大表、一个小表做关联查询时,一定要建立好索引。

 

MySQL用户管理

1. 用户添加及分配权限

1)登录mysql

#mysql  -uroot –p

 2)添加用户

 mysql> user mysql   //使用mysql数据库

mysql> grant all on *.* to zhangchuan@”%” identified by “123″ ;

 (执行完会在mysql.user表插入一条记录,all表示所有权限(包括增 删 改 查

等权限); *.* 表示所有数据库,zhangchuan为添加的用户名,123为密码,

%为匹配的所有主机,上面的信息都可以指定如grant select,update on db.* to

zhangchuan @localhost identified by ’123″;)

mysql> flush privileges;  //刷新系统权限表

3)修改用户

mysql> update user set user=’mysqladminqwe’ where user=’root’;

mysql> flush privileges;

4)删除用户

mysql> delete from mysql.user where user =’zhangchuan’ ;

mysql> flush privileges;

注:执行完sql语句后必须执行 flush privileges才可生效

2. 密码修改:

1)使用mysqladmin命令

# mysqladmin -u root password oldpass “newpass”

2)登录mysql客户端操作mysql.user

#mysql –u root

mysql> user mysql

mysql> UPDATE user SET Password = PASSWORD(‘newpass’) WHERE user = ‘root’;

mysql> flush privileges;

3. 忘记密码

1) 停止mysql服务

# /etc/init.d/mysqld stop

或: service mysqld stop

2)进入mysql的bin目录执行以下操作

# cd /usr/local/mysql/bin

# ./mysqld_safe –user=mysql –skip-grant-tables –skip-networking &

3)进入mysql客户端修改密码

# mysql -u root mysql

mysql> UPDATE user SET Password=PASSWORD(‘newpassword’)

where USER=’root’;

mysql> FLUSH PRIVILEGES;

mysql> quit

4)启动mysql服务登录

# /etc/init.d/mysql restart

# mysql -uroot -p

Enter password: <输入新设的密码newpassword>

4. 开放外网权限

上面已经说过,可以使用grant命令,不过我遇到使用grant开放权限后,使用本地客户端却连接不上。

解决方法如下:

1)检查3306端口是否开放给指定IP

使用nestat命令查看3306端口状态:

~# netstat -an | grep 3306

tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN

若出现以上结果表示3306端口只是在IP 127.0.0.1上监听,所以拒绝了其他IP的访问。

 2)修改/etc/mysql/my.cnf文件。打开文件,找到下面内容:

# Instead of skip-networking the default is now to listen only on

# localhost which is more compatible and is not less secure.

bind-address = 127.0.0.1

把上面这一行注释掉或者把127.0.0.1换成合适的IP,建议注释掉。

重新启动后,重新使用netstat检测:

~# netstat -an | grep 3306

tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN

清除mysql数据同步文件

1. 按文件:删除mysqld-bin.000123之前的日志,不包含mysqld-bin.000123

MYSQL>purge binary logs to ‘mysqld-bin.000123′;

Query OK, 0 rows affected (0.16 sec)

2. 按时间:删除2012-12-20 00:00:00 之前的日志

MYSQL>purge binary logs before ’2012-12-20 00:00:00′;

3. 按时间:请理三天之前的日志

MYSQL> purge master logs before date_sub(now(), interval 3 day);

自动清理日志 :

4. 修改my.cnf文件配置bin-log过期时间

[mysqld]

expire-logs-days=3

表示删除3天之前的数据

 

 

mysql存储引擎InnoDB与MyISAM比较

 

InnoDBMyisam的六大区别

MyISAM InnoDB
构 成上的区别: 每个MyISAM在磁盘上存储成三个文件。第一个 文件的名字以表的名字开始,扩展名指出文件类型。.frm文件存储表定义。数据文件的扩 展名为.MYD (MYData)。索引文件的扩 展名是.MYI (MYIndex)。MyISAM备份数据只需要将以上三个文件导出即可,Innodb需要导出为sql语句 基于磁盘的资源是InnoDB表空间数据文件和它的日志文件,InnoDB 表的 大小只受限于操作系统文件的大小,一般为 2GB
事务处理上方面: MyISAM类型的表强调的是性能,其执行数 度比InnoDB类型更快,但是不提供事务支持 InnoDB提供事务支持事务,外部键等高级 数据库功能
SELECT UPDATE,INSERTDelete操 作 如果执行大量的SELECT,MyISAM是更好的选择 1.如果你的数据执行大量的INSERTUPDATE,出于性能方面的考虑,应该使用InnoDB表2.DELETE FROM table时,InnoDB不会重新建立表,而是一行一行的 删除。3.LOAD TABLE FROM MASTER操作对InnoDB是不起作用的,解决方法是首先把InnoDB表改成MyISAM表,导入数据后再改成InnoDB表,但是对于使用的额外的InnoDB特性(例如外键)的表不适用
AUTO_INCREMENT的 操作 每表一个AUTO_INCREMEN列的内部处理。MyISAMINSERTUPDATE操 作自动更新这一列。这使得AUTO_INCREMENT列更快(至少10%)。在序列顶的值被删除之后就不 能再利用。(当AUTO_INCREMENT列被定义为多列索引的最后一列, 可以出现重使用从序列顶部删除的值的情况)。AUTO_INCREMENT值可用ALTER TABLE或myisamch来重置对于AUTO_INCREMENT类型的字段,InnoDB中必须包含只有该字段的索引,但 是在MyISAM表中,可以和其他字段一起建立联 合索引更好和更快的auto_increment处理 如果你为一个表指定AUTO_INCREMENT列,在数据词典里的InnoDB表句柄包含一个名为自动增长计数 器的计数器,它被用在为该列赋新值。自动增长计数 器仅被存储在主内存中,而不是存在磁盘上关于该计算器 的算法实现,请参考AUTO_INCREMENT列 在InnoDB里 如何工作
表 的具体行数 select count(*) from table,MyISAM只要简单的读出保存好的行数,注意的是,当count(*)语句包含 where条件时,两种表的操作是一样的 InnoDB 中不 保存表的具体行数,也就是说,执行select count(*) from table时,InnoDB要扫描一遍整个表来计算有多少行
表锁 提供行锁(locking on row level),提供与 Oracle 类型一致的不加锁读取(non-locking read in
SELECTs),另外,InnoDB表的行锁也不是绝对的,如果在执 行一个SQL语句时MySQL不能确定要扫描的范围,InnoDB表同样会锁全表,例如update table set num=1 where name like “%a%”