MYSQL使用集锦

技术库 小李 1162浏览
  • 如何判断一个字段是否包含某些字符?

如数据表中有字段 f=’a,b,c,d,e’,现有字符$str=’o,d,l,i’ 中某一字符包含在字段中,可用方法:

使用like,locate,find_in_set,拼接形式相同  : f like '%o%' or f like '%d%' ...
使用REGEXP正则方式最简洁:f REGEXP ('o|d|l|i'),需要将“,”转换为“|”
  • mysql导入导出
导出数据库:mysqldump -hhostname -uusername -ppassword databasename > /data/databasename.sql
导出数据结构:mysqldump -hhostname -uusername -ppassword -d databasename > /data/databasename.sql
导出存储过程:mysqldump -hhostname -uusername -ppassword -ntd -R databasename > prorandfunc.sql
导出一个表:mysqldump -d dbname1 tablename1 -u root -p > xxx.sql
导出多个表:mysqldump -d -B dbname1 --tables tablename1 tablename2 -u root -p > xxx.sql
导入:mysql -uroot -h192.168.1.222 -pxxxxxx db_xxxxx < xxxx.sql
  • 命令参数
-h 主机
-P端口
-u用户名
-p密码 (注意不能有空格)
--ignore-table   忽略表不到出 举例  ----ignore-table=dataname.table1 --ignore-table=dataname.table2
-d 结构(--no-data:不导出任何数据,只导出数据库表结构)
-t 数据(--no-create-info:只导出数据,而不添加CREATE TABLE 语句)
-n (--no-create-db:只导出数据,而不添加CREATE DATABASE 语句)
-R (--routines:导出存储过程以及自定义函数)
-E (--events:导出事件)
--triggers (默认导出触发器,使用--skip-triggers屏蔽导出)
-B (--databases:导出数据库列表,单个库时可省略)
--tables 表列表(单个表时可省略)
①同时导出结构以及数据时可同时省略-d和-t
②同时 不 导出结构和数据可使用-ntd
③只导出存储过程和函数可使用-R -ntd
④导出所有(结构&数据&存储过程&函数&事件&触发器)使用-R -E(相当于①,省略了-d -t;触发器默认导出)
⑤只导出结构&函数&事件&触发器使用 -R -E -d
  • 优化 LIKE 模糊查询

mysql 高效模糊查询 代替like

而对于xxx% 或者%xxx方式,explain一下可以发现查询使用到了索引,性能提升了不少,当然这种方式不适用与所有的查询场景。
可以采取以下的函数进行查询。
LOCATE('substr',str,pos)方法
POSITION('substr' IN field)方法
INSTR(str,'substr')方法

查询效率比如果:table.field like ‘%AAA%’ 可以改为locate (‘AAA’ , table.field) > 0

返回子串substr在字符串str第一个出现的位置,如果substr不是在str里面,返回0.
mysql> select LOCATE(‘bar’, ‘foobarbar’); -> 4 mysql> select LOCATE(‘xbar’, ‘foobar’); -> 0

该函数是多字节可靠的。

返回子串substr在字符串str第一个出现的位置,从位置pos开始。如果substr不是在str里面,返回0。
mysql> select LOCATE(‘bar’, ‘foobarbar’,5); -> 7

mysql like模糊查询提高效率的奇葩方法

一张表大概40万左右的数据,用like模糊查询title字段,很慢,title字段已经建立了索引,mysql 对 someTitle% 这样的模糊查询在有索引的前提下是很快的。
所以下面这两台sql语句差别就很大了
$sql1 = “…… title like someTitle%” (话费0.001秒)
$sql2 = “…… title like %someTitle%” (话费0.8秒)
这两句的效率相差了800倍,这很可观啊。
所以我有个想法:在不用分词的方法的前提下,把存储的title字段,加一个特别的前缀,比如”im_prefix”,比如一条记录的title=”我是标题党”,那么存储的时候就存储为”im_prefix我是标题党”。
这样一来,我们要模糊查找”标题党”这个关键词的时候,就把sql写成这样:
$sql1 = “…… title like im_prefix%标题党%” (花费0.001秒),前台显示数据的时候,自然把取到的title过滤掉”im_prefix”这个前缀了

  • MYSQL5.6下直接复制innodb方法

在新数据库中创建一样的数据表: ky_rank

再执行alter table ky_rank discard tablespace;

将原数据库中的表 ky_rank.ibd复制到新数据库

再执行alter table ky_rank import tablespace;

会有错误提示,但可以打开

  • 关联更新删除不走索引优化

update user1 u set u.reid=(select id from user2 u2 where u1.code=u2.code)

解释执行计划中的select_type中的dependent subquery的检索过程

优化改写为以下join方式

update user1 u ,user2 u2 set u.reid=u2.id

转载请注明:清韵逸-博客生活分享 » MYSQL使用集锦