Mysql知识学习

PHPer 2020-04-10 1333 0 0

MySQL 8.0 索引特性1-函数索引...

立即注册

更新于:2020-04-10 21:18:34
    您需要登录后才可以评论。 立即注册

    MySQL 8.0 索引特性1-函数索引

    MySQL 8.0 索引特性1-函数索引

    函数索引顾名思义就是加给字段加了函数的索引,这里的函数也可以是表达式。所以也叫表达式索引。MySQL 5.7 推出了虚拟列的功能,MySQL8.0的函数索引内部其实也是依据虚拟列来实现的。

    我们考虑以下几种场景:

    1.对比日期部分的过滤条件

    SELECT ...
    FROM tb1
    WHERE date(time_field1) = current_date;
    	

    2.两字段做计算

    SELECT ...
    FROM tb1
    WHERE field2 + field3 = 5;
    	

    3.求某个字段中间某子串

    SELECT ...
    FROM tb1
    WHERE substr(field4, 5, 9) = 'actionsky';
    	

    4.求某个字段末尾某子串

    SELECT ...
    FROM tb1
    WHERE RIGHT(field4, 9) = 'actionsky';
    	

    5.求JSON格式的VALUE

    SELECT ...
    FROM tb1
    WHERE CAST(field4 ->> '$.name' AS CHAR(30)) = 'actionsky';
    	

    以上五个场景如果不用函数索引,改写起来难易不同。不过都要做相关修改,不是过滤条件修正就是表结构变更添加冗余字段加额外索引。

    比如第1个场景改写为,

    SELECT ...
    FROM tb1
    WHERE time_field1 >= concat(current_date, ' 00:00:00')
        AND time_field1 <= concat(current_date, '23:59:59');
    	

    再比如第4个场景的改写,由于是求最末尾的子串,只能添加一个新的冗余字段,并且做相关的计划任务来一定频率的异步更新或者添加触发器来实时更新此字段值

    SELECT ...
    FROM tb1
    WHERE field4_suffix = 'actionsky';
    	

    那我们看到,改写也可以实现,不过这样的SQL就没有标准化而言,后期不能平滑的迁移了。

    MySQL 8.0 推出来了函数索引让这些变得相对容易许多。不过函数索引也有自己的缺陷,就是写法很固定,必须要严格按照定义的函数来写,不然优化器不知所措。

    我们来把上面那些场景实例化。示例表结构,

    5ed85bafca_normal.png

    总记录数

    mysql> SELECT COUNT(*)
    FROM t_func;
    +----------+
    | count(*) |
    +----------+
    |    16384 |
    +----------+
    1 row in set (0.01 sec)
    	

    我们把上面几个场景的索引全加上

    mysql > ALTER TABLE t_func ADD INDEX idx_log_time ( ( date( log_time ) ) ),
    ADD INDEX idx_u1 ( ( rank1 + rank2 ) ),
    ADD INDEX idx_suffix_str3 ( ( RIGHT ( str3, 9 ) ) ),
    ADD INDEX idx_substr_str1 ( ( substr( str1, 5, 9 ) ) ),
    ADD INDEX idx_str2 ( ( CAST( str2 ->> '$.name' AS CHAR ( 9 ) ) ) );
    QUERY OK,
    0 rows affected ( 1.13 sec ) Records : 0 Duplicates : 0 WARNINGS : 0
    	

    我们再看下表结构, 发现好几个已经被转换为系统自己的写法了

    04f3818a8f_normal.png

    MySQL 8.0 还有一个特性,就是可以把系统隐藏的列显示出来。我们用show extened 列出函数索引创建的虚拟列,

    d2b3f2da86_normal.png

    上面5个随机字符串列名为函数索引隐式创建的虚拟COLUMNS。

    我们先来看看场景2,两个整形字段的相加,

    mysql> SELECT COUNT(*)
    FROM t_func
    WHERE rank1 + rank2 = 121;
    +----------+
    | count(*) |
    +----------+
    |      878 |
    +----------+
    1 row in set (0.00 sec)
    	

    看下执行计划,用到了idx_u1函数索引,

    mysql> explain SELECT COUNT(*)
    FROM t_func
    WHERE rank1 + rank2 = 121\G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: t_func
       partitions: NULL
             type: ref
    possible_keys: idx_u1
              key: idx_u1
          key_len: 9
              ref: const
             rows: 878
         filtered: 100.00
            Extra: NULL
    1 row in set, 1 warning (0.00 sec)
    	

    那如果我们稍微改下这个SQL的执行计划,发现此时不能用到函数索引,变为全表扫描了,所以要严格按照函数索引的定义来写SQL ...

    立即注册
    更新于:2020-04-10 21:20:21

    MySQL字符串函数substring:字符串截取

    substr() 等价于 substring() 函数,我这里主要是看下这个函数,并应用。

    http://www.cnblogs.com/zdz8207/p/3765073.html

    MySQL 字符串截取函数:left(), right(), substring(), substring_index()。还有 mid(), substr()。其中,mid(), substr() 等价于 substring() 函数,substring() 的功能非常强大和灵活。

    1. 字符串截取:left(str, length)

    mysql> select left('example.com', 3);
    +-------------------------+
    | left('example.com', 3) |
    +-------------------------+
    | exa |
    +-------------------------+
    2. 字符串截取:right(str, length)

    mysql> select right('example.com', 3);
    +--------------------------+
    | right('example.com', 3) |
    +--------------------------+
    | com |
    +--------------------------+

    实例:

    #查询某个字段后两位字符
    select right(last3, 2) as last2 from historydata limit 10;
    #从应该字段取后两位字符更新到另外一个字段
    update `historydata` set `last2`=right(last3, 2);


    3. 字符串截取:substring(str, pos); substring(str, pos, len)

    3.1 从字符串的第 4 个字符位置开始取,直到结束。

    mysql> select substring('example.com', 4);
    +------------------------------+
    | substring('example.com', 4) |
    +------------------------------+
    | mple.com |
    +------------------------------+
    3.2 从字符串的第 4 个字符位置开始取,只取 2 个字符。

    mysql> select substring('example.com', 4, 2);
    +---------------------------------+
    | substring('example.com', 4, 2) |
    +---------------------------------+
    | mp |
    +---------------------------------+
    3.3 从字符串的第 4 个字符位置(倒数)开始取,直到结束。

    mysql> select substring('example.com', -4);
    +-------------------------------+
    | substring('example.com', -4) |
    +-------------------------------+
    | .com |
    +-------------------------------+
    3.4 从字符串的第 4 个字符位置(倒数)开始取,只取 2 个字符。...

    立即注册
    更新于:2020-04-21 00:50:47

    Mysql的临时变量取值3例

    在实际使用时,我在mysql8下测试的是不用set声明,直接用 select @mysum:=COUNT(*) from mendpinfo where ipaddr = rvIpaddr;这个语句 因为有一个临时的结果,就是查找关键词的位置要存下,所以用到了临时变量,注意用的时候前面要加@ 20200422

    例子1:

    DECLARE iSystemTypeId INT;

    set iSystemTypeId = NULL;

    select SystemTypeId into iSystemTypeId from B_SystemType where os = Aos;

    常规用法

    例子2:

    DECLARE lockTime datetime;

    set lockTime=(select lockTime from ccmUsers where name=myuser);

    trigger中用的多

    例子3:

    set @mysum=0;

    select @mysum:=COUNT(*) from mendpinfo where ipaddr = rvIpaddr;...

    立即注册
    更新于:2020-04-21 00:57:37

    mysql查找字符串出现位置

    我这里就是用下LOCATE(substr,str)查找字符串在字段中出现的位置。

    MySQL中的LOCATE和POSITION函数使用方法

    FIND_IN_SET(str,strlist)

    假如字符串str 在由N 子链组成的字符串列表strlist 中,则返回值的范围在 1 到 N 之间。一个字符串列表就是一个由一些被‘,’符号分开的自链组成的字符串。如果第一个参数是一个常数字符串,而第二个是type SET列,则 FIND_IN_SET() 函数被优化,使用比特计算。如果str不在strlist 或strlist 为空字符串,则返回值为 0 。如任意一个参数为NULL,则返回值为 NULL。这个函数在第一个参数包含一个逗号(‘,’)时将无法正常运行。返回值为str在strlist中的位置,从1开始计数。

    LOCATE(substr,str)

    POSITION(substr IN str)

    函数返回子串substr在字符串str中第一次出现的位置。如果子串substr在str中不存在,返回值为0。

    str IN (strlist)

    查找str在strlist中出现的位置。如果找不到,返回false。strlist为一个用逗号连接的字符串。

    函数的区别为:第一个函数FIND_IN_SET中的strlist为一个用逗号连接起来的字符串,一般为数据库中的某个字段。当需要查找某个字段中是否有某个值的时候,使用这个函数。

    第三个函数IN()刚好和函数FIND_IN_SET()相反,strlist为一个常量字符串序列,str为数据库中某个字段。此时查找数据库中的字段是否在某个序列中。...

    立即注册
    更新于:2022-08-15 00:56:08

    mysql 更改AUTO_INCREMENT 失败的解决办法

    SET FOREIGN_KEY_CHECKS = 0; ALTER TABLE仓库`.addresses` 更改列`aID``aID` INT(10)UNSIGNED NOT NULL; SET FOREIGN_KEY_CHECKS = 1;

    首先,从ID列中删除 AUTO_INCREMENT 。如果有外键还有更改外键,我已经检查了外键,所以我必须运行:

    SET FOREIGN_KEY_CHECKS = 0; 
     ALTER TABLE仓库`.addresses` 
    更改列`aID``aID` INT(10)UNSIGNED NOT NULL; 
     SET FOREIGN_KEY_CHECKS = 1;
    

    最后,重新添加 AUTO_INCREMENT : ,或者更新AUTO_INCREMENT后再添加 ...

    立即注册
    更新于:2021-03-16 06:58:53

    MYSQL 数据库导入导出命令

    mysqldump -u root -p wokan > /data/wokan20211224.sql 在操作系统命令行 运行 mysqldump -u root -p test >d:\test.sql

    在不同操作系统或MySQL版本情况下,直接拷贝文件的方法可能会有不兼容的情况发生。所以一般推荐用SQL脚本形式导入。下面分别介绍两种方法。

    MySQL命令行导出数据库

    1,进入MySQL目录下的bin文件夹:cd MySQL中到bin文件夹的目录

    如我输入的命令行:cd C:\Program Files\MySQL\MySQL Server 4.1\bin

    (或者直接将windows的环境变量path中添加该目录)

    2,导出数据库:mysqldump -u 用户名 -p 数据库名 > 导出的文件名

    如我输入的命令行:mysqldump -u root -p news > news.sql(输入后会让你输入进入MySQL的密码)

    (如果导出单张表的话在数据库名后面输入表名即可)

    3、会看到文件news.sql自动生成到bin文件下

    命令行导入数据库

    1,将要导入的.sql文件移至bin文件下,这样的路径比较方便
    2,同上面导出的第1步
    3,进入MySQL:mysql -u 用户名 -p

    如我输入的命令行:mysql -u root -p(输入同样后会让你输入MySQL的密码)

    4,在MySQL-Front中新建你要建的数据库,这时是空数据库,如新建一个名为news的目标数据库
    5,输入:mysql>use 目标数据库名

    如我输入的命令行:mysql>use news;

    6,导入文件:mysql>source 导入的文件名;

    如我输入的命令行:mysql>source news.sql;

    MySQL备份和还原,都是利用mysqldump、mysql和source命令来完成的。

    备份数据库:
    进入cmd
    导出所有数据库:输入:mysqldump -u [数据库用户名] -p -A>[备份文件的保存路径]

    导出数据和数据结构:输入:mysqldump -u [数据库用户名] -p [要备份的数据库名称]>[备份文件的保存路径]
    例子:mysqldump -u root -p test>d:\test.sql
    注意:此备份只备份数据和数据结构,没有备份存储过程和触发器

    只导出数据不导出数据结构:输入:mysqldump -u [数据库用户名] -p -t [要备份的数据库名称]>[备份文件的保存路径]

    导出数据库中的Events
    输入:mysqldump -u [数据库用户名] -p -E [数据库用户名]>[备份文件的保存路径]

    导出数据库中的存储过程和函数
    mysqldump -u [数据库用户名] -p -R [数据库用户名]>[备份文件的保存路径]

    导入数据库
    mysql -u root -p<[备份文件的保存路径] 疑问

    恢复备份文件:
    进入MYSQL Command Line Client
    先创建数据库:create database test 注:test是创建数据库的名称
    再切换到当前数据库:use test
    再输入:\. d:/test.sql 或 souce d:/test.sql

    1. 概述
    MySQL数据库的导入,有两种方法:
    1) 先导出数据库SQL脚本,再导入;
    2) 直接拷贝数据库目录和文件。

    在不同操作系统或MySQL版本情况下,直接拷贝文件的方法可能会有不兼容的情况发生。
    所以一般推荐用SQL脚本形式导入。下面分别介绍两种方法。

    2. 方法一 SQL脚本形式
    操作步骤如下:
    2.1. 导出SQL脚本
    在原数据库服务器上,可以用phpMyAdmin工具,或者mysqldump(mysqldump命令位于mysql/bin/目录中)命令行,导出SQL脚本。
    2.1.1 用phpMyAdmin工具
    导出选项中,选择导出“结构”和“数据”,不要添加“Drop DATABASE”和“Drop TABLE”选项。
    选中“另存为文件”选项,如果数据比较多,可以选中“gzipped”选项。
    将导出的SQL文件保存下来。

    2.1.2 用mysqldump命令行
    命令格式
    mysqldump -u用户名 -p 数据库名 > 数据库名.sql
    范例:
    mysqldump -uroot -p abc > abc.sql
    (导出数据库abc到abc.sql文件)

    提示输入密码时,输入该数据库用户名的密码。

    2.2. 创建空的数据库
    通过主控界面/控制面板,创建一个数据库。假设数据库名为abc,数据库全权用户为abc_f。

    2.3. 将SQL脚本导入执行
    同样是两种方法,一种用phpMyAdmin(mysql数据库管理)工具,或者mysql命令行。
    2.3.1 用phpMyAdmin工具
    从控制面板,选择创建的空数据库,点“管理”,进入管理工具页面。
    在"SQL"菜单中,浏览选择刚才导出的SQL文件,点击“执行”以上载并执行。

    注意:phpMyAdmin对上载的文件大小有限制,php本身对上载文件大小也有限制,如果原始sql文件
    比较大,可以先用gzip对它进行压缩,对于sql文件这样的文本文件,可获得1:5或更高的压缩率。
    gzip使用方法:
    # gzip xxxxx.sql
    得到
    xxxxx.sql.gz文件。

    2.3.2 用mysql命令行
    命令格式
    mysql -u用户名 -p 数据库名 < 数据库名.sql
    范例:
    mysql -uabc_f -p abc < abc.sql
    (导入数据库abc从abc.sql文件)

    提示输入密码时,输入该数据库用户名的密码。

    3 方法二 直接拷贝
    如果数据库比较大,可以考虑用直接拷贝的方法,但不同版本和操作系统之间可能不兼容,要慎用。
    3.1 准备原始文件
    用tar打包为一个文件

    3.2 创建空数据库

    3.3 解压
    在临时目录中解压,如:
    cd /tmp
    tar zxf mydb.tar.gz

    3.4 拷贝
    将解压后的数据库文件拷贝到相关目录
    cd mydb/
    cp * /var/lib/mysql/mydb/

    对于FreeBSD:
    cp * /var/db/mysql/mydb/

    3.5 权限设置
    将拷贝过去的文件的属主改为mysql:mysql,权限改为660
    chown mysql:mysql /var/lib/mysql/mydb/*
    ...

    立即注册
    更新于:2021-12-24 20:19:55

    mysql如何判断不包含某个字符串

    mysql中可以使用locate()函数判断不包含某个字符串。

    locate()函数判断字符串(string)中是否包含另一个字符串(subStr):

    locate(subStr,string) :函数返回subStr在string中出现的位置

    使用locate(字符,字段名)函数,如果包含,返回>0的数,否则返回0 。

    // 如果字符串 string 包含 subStr
    locate(subStr,string) > 0
    // 如果字符串 string 不包含 subStr
    locate(subStr,string) = 0
    

    测试了发觉 locate(subStr,string) = 0 执行效率有点慢 20220414 ...

    立即注册
    更新于:2022-04-14 17:36:10

    Mysql中不要用操作符和函数名等等来命名字段

    Mysql中不要用操作符和函数名等等来命名字段...

    立即注册
    更新于:2022-04-14 23:37:50
    相关内容

    Linux脚本和脚本知识

    一键安装TiDB开发环境(centos7)

    php知识点学习整理

    PHP preg_quote() 函数

    PHP str_ireplace() 函数

    查看php位置

    php7.4 查看版本

    PHP提示 cURL error 60: SSL certificate problem: unable to get loca...

    php过滤表单输入的emoji表情

    PHP 中英文混排截取字符串 (用php自带的函数,简单效果又好)

    PHP的源代码BUG整理

    php如何查看扩展是否开启

    linux下安装php的Exif扩展笔记!(用LNMP一键安装脚本安装的php)

    configure: error: Cannot find ldap.h 解决方法

    一个PHP针对数字的加密解密类

    Mysql问题整理

    Yii2数据库报错-SQLSTATE[HY093]: Invalid parameter number: no para...

    Mysql server has gone away 报错原因分析及解决办法

    mysql 警告 could not be resolved: Name or service not known

    Mysql用特殊字符设置密码遇到的问题

    Mysql的函数substring使用注意事项

    MySQL 5.7内存使用分析

    mysql 命令整理

    【mysql】主键、普通索引、唯一索引和全文索引的比较

    没有接收到要导入的数据。可能是文件名没有提交,也可能是文件大小超出 PHP 限...

    Mysql 报错'SQLSTATE[HY000] [1045] Access denied for user 'root'@'...

    php连接mysql报错The server requested authentication method unknown to ...

    Mysql登录提示ERROR 2003 (HY000): Can't connect to MySQL server o...

    Mysql报错SQLSTATE[HY000]: General error: 1366 Incorrect string ...

    全网最详尽的Centos和Windows系统 Mysql8 root忘记密码解决办法

    mysql数据库操作 报错 #1030 - Got error 176 "Read page with wrong ...

    Linux系统彻底卸载MySQL数据库

    Linux下mysql5.7忘记root密码的解决方法

    ERROR 1130 (HY000): Host '127.0.0.1' is not allowed to connect to...

    CSS3知识整理

    transition属性-CSS3知识整理

    Bootstrap-排版

    Bootstrap-响应式工具

    CSS3的rem详解及使用方法 - CSS3知识整理

    推荐内容

    分享几个好用的bt搜索bt资源下载网址网站

    影视电影剧集动漫综艺bt资源在线播放网址网站推荐分享

    全网最新bt磁力搜索引擎bt资源bt网站网址分享

    怎样使用V2Ray代理和SSTap玩如魔兽世界/绝地求生/LOL台服/战地3/黑色沙漠/彩...

    sstap游戏代理教程 从此玩如魔兽世界/绝地求生/LOL台服/战地3/黑色沙漠/彩虹六...

    人气美女女优百度指数排行榜(波多野结衣,苍井空,三上悠亚,深田咏美,桥本有菜,北条...

    最强人造人深田咏美,业界的社交女王

    人生起起落落的三上悠亚,成名前的清纯风,怎么搭配出来的?

    业内第一大长腿,桥本有菜的腿究竟有多长

    波多野结衣,岛国业界著作最多的超级劳模

    日本AV界NO.1,苍井空是多少宅男的疯狂?

    美女女优视频混剪 This Girl-Laza Morgan

    使用V2Ray的mKCP协议加速游戏