分类 mysql 下的文章

看我如何解决自增ID带来的麻烦

自增ID还有麻烦?
有啊, 自增可预测,很方便采集的人员。
如何解决这种麻烦呢?
看我运用 mysql的函数,视图来解决这个问题。
上代码:

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`%` 
    SQL SECURITY DEFINER
VIEW `txt_ms` AS
    SELECT 
        相关字段,
        CAID(`txt`.`id`) AS `mid`
    FROM
        `txt`
    WHERE
        (`txt`.`status` = 1)

视图txt_ms创建好后,调数据 就像调 txt 一样,只是多了一个 字段 mid,而mid 是通过自定义函数 CAID得到的
CAID的代码:
由于代码避免敏感,需要按照自建的算法来生成 这里就不写了, 自定函数的方法 可以参考
mysql函数的应用

mysql8 安装与更改datadir数据目录

先上一张安装过程的输出日志:
安装log

sudo apt install mysql-server

完全干净的删除 mysql 用

sudo apt remove mysql* --purge

从日志来看, 安装过程中,显示创建一个root帐号然后 因为IO interfaces failed和mysqlx.sock failed 重启 逐个解决了这2个问题后安装并且启动完成。
现在我们来讲讲更改datadir的操作,经过卸载重安装多次循环操作后掌握了其更改datadir的核心技术^_^
关于更改datadir 之前我有做过记录mysql 8 更改数据存放目录,不过现在再用这个办法已经不适用了,于是乎有了这新的篇章。

 vi /etc/mysql/mysql.conf.d/mysqld.cnf

增加 datadir 的设置 如设置为:

datadir = /data/mydata/

设置 mysqld.cnf
设置好后 :x 或者:wq 保存退出

这次其实就多了一步 就是设置 apparmor 设置好这里的别名,设置后就好了。

vi /etc/apparmor.d/tunables/alias

打开后插入一行 如下:/data/mydata/ 为你希望的datadir

  alias /var/lib/mysql/ -> /data/mydata/,

设置 apparmor
值得注意的是:上面那个逗号不能少
设置好后 :x 或者:wq 保存退出

sudo systemctl restart apparmor

或者

sudo service apparmor restart

然后初始化一下

sudo mysqld --initialize

或者

sudo mysqld --initialize-insecure

--initialize-insecure 将初始化一个没有密码的root帐号

最后 启动 mysql

sudo service mysql start

mysql之函数的应用

先上代码

DELIMITER $$
CREATE DEFINER=`root`@`%` FUNCTION `aa`(ids int,tim varchar(16)) RETURNS int(11)
BEGIN
declare aaa int; 
if tim>0 then
set @tim=tim; 
set @st = UNIX_TIMESTAMP(@tim);
set @et=UNIX_TIMESTAMP(DATE_ADD(@tim, INTERVAL 1 MONTH)); 
select sum(m) into aaa from(
SELECT 
        `money_out`.`bid` AS `bid`,
        (SUM(`money_out`.`money`) / 100) AS `m`
    FROM
        `money_out`
        where bid=ids and ctime>=@st and ctime<@et
        union
        select
    `money_out_book`.`bid` AS `bid`,
        (SUM(`money_out_book`.`money`) / 100) AS `m`
    FROM
        `money_out_book`
        where bid=ids and ctime>=@st and ctime<@et)  a;
else
select sum(m) into aaa from(
SELECT 
        `money_out`.`bid` AS `bid`,
        (SUM(`money_out`.`money`) / 100) AS `m`
    FROM
        `money_out`
        where bid=ids
        union
        select
    `money_out_book`.`bid` AS `bid`,
        (SUM(`money_out_book`.`money`) / 100) AS `m`
    FROM
        `money_out_book`
        where bid=ids)  a;
    end if;
RETURN aaa;
END$$
DELIMITER ;

功能:该函数...先不说。

 CREATE DEFINER=`root`@`%` FUNCTION `aa`(ids int,tim varchar(16)) RETURNS int(11)

创建一个函数名称是 aa 可以接收 2个参数 ids,和 tim 返回 一个整型的数据。

 declare aaa int; 

定义一个变量

set @tim=tim; 
set @st = UNIX_TIMESTAMP(@tim);
set @et=UNIX_TIMESTAMP(DATE_ADD(@tim, INTERVAL 1 MONTH));

输入参数的传递 与转换

select sum(m) into aaa from(...) tmp  

把查询到的数据写入到变量 aaa

RETURN aaa;

返回aaa

OK,这个函数的功能是 计算给定的 书ID和时间 返回 对应的 的消费情况。如果有时间输入 则返回指定时间的数据, 否则返回该书所有的数据。
这里是关于mysql 触发器的应用

用触发器来记录操作记录

直接上代码

DROP TRIGGER IF EXISTS `trigger_book` ;

DELIMITER $$
CREATE DEFINER=`root`@`%` TRIGGER trigger_book after update ON book FOR EACH ROW 
BEGIN  
declare nowtime int(11); 
set @b1 ='';
set nowtime=unix_timestamp(now());
if (old.cooperation!=new.cooperation or new.base!=old.base or new.buyout!=old.buyout or new.into!=old.into or new.quan!=old.quan) then
    if (old.cooperation!=new.cooperation) then
    set @b1=concat(" 修改签约类型为:",new.cooperation," 旧值:",old.cooperation);
    end if;
    if (new.base!=old.base) then
        set @b1=concat(@b1," 修改保底为:",new.base," 旧值:",old.base);
    end if;
    if (new.buyout!=old.buyout) then
        set @b1=concat(@b1," 修改买断为:",new.buyout," 旧值:",old.buyout);
    end if;
    if (new.quan!=old.quan) then
        set @b1=concat(@b1," 修改全勤为:",new.quan," 旧值:",old.quan);
    end if;
    if (new.`into`!=old.`into`) then
        set @b1=concat(@b1," 修改分成为:",new.`into`," 旧值:",old.`into`);
    end if;
insert into opt_log(opt_uid,opt_value,opt_time,bid)value(new.opt_uid,@b1,nowtime,new.id);
end if;
end
$$#
DELIMITER ;

以上是修改书的签约类型时,记录对应的人以及对应的修改前后的值。

可能应用场景有限,但是这不需要在应用层去做相关功能, 直接数据库里就完事了。

如果感兴趣还可以看看函数的应用

You cannot use the alias '' of an expression containing a window function in this context.

抛出这个错误一般是你使用窗口函数后直接在where子句里引用了窗口函数的结果,但是窗口函数的计算是在where子句之后才执行的,所以sql会抛出异常 ,那么该怎么解决这个问题呢?

1.使用子查询
2.使用 with的方法

如:
子查询的方式

select title,(select name from clas where id=top.cid)cname,view from (SELECT title,cid,view,row_number () over ( PARTITION BY `cid` ORDER BY view desc ) t  from book)top where t=1

with的方式

with top as(
SELECT title,cid,view,row_number () over ( PARTITION BY `cid` ORDER BY view desc ) t  from book)
select title,(select name from clas where id=top.cid)cname,view from top where t=1;

上述语句是从数据库中查询每个分类中点击最多的书,由此可以优雅的看出各个分类中那一本书最受欢迎。
而如果我们使用group by 来做类似的求值 一般会出现only_full_group_by的错误提示,而且也不好处理