mariadb_mariadb 存储过程-CSDN博客

本文链接：https://blog.csdn.net/a_a_run/article/details/126180789

mariadb

sql相关

15

2020-11-13
mariaDB 10.1.45
编写存储过程经验：
declare关键字只能用在存储过程中，存储过程需要
delimiter //
drop procedure if exists myP()；【这里必须有分号】
create procedure myP()【这里注意一定要有括号同时不能有分号】
begin
declare endTemp varchar(20) 【这里注意一定要有类型定义和长度】default ‘’；【begin里面的语句要有分号】
…
end //
delimiter ;

存储过程注意事项：cursor定义要在handler之前
declare myCursor CURSOR FOR SELECT TRAIN_ID FROM MY_TABLE;这里最后一定要有分号
declare continue handler for not found set endFlag=1;
这句一定要在declare myCursor cursor…和 open myCursor之间

mysql若字段排序规则不同，则执行存储过程时，string类型变量赋值给另一个string类型变量会报错，修改方法：navicat右键数据库/编辑链接/选择排序规则->OK了

17

mysql查询结果集设定自增序列
select (@i:=@i+1),t.id from (select *,@i:=0 as i from syst_user) t;

18

记录一个花了很久时间查询原因的问题：（其实这里还不是很确定，下面按train_id去查，如果我在之前的查询结果集中新增几个日期和结果集相同但是id递增的数据，再次查询居然是按id升序排序而且刚才新增的几个数据却是排在查询结果集最后，且按新增顺序倒叙排列（这里一定要注意是手动新增顺序，即使新增的方式是复制，也就是说新增的数据的创建以及更新时间一样），即后新增的排在先新增的前面，

例如新增前
200,2021-06-25
201,2021-06-25
新增下列数据
300,2021-06-25
400,2021-06-25
再次查询变成
200,2021-06-25
201,2021-06-25
400,2021-06-25
300,2021-06-25

上面数据按id查询结果居然和之前按train_id去查顺序一样,包括新增的也一样

后来看了，关键是看2次查询使用的索引（explain中的extra）是否一致，使用的索引一致返回结果集顺序就一致
参考：
https://blog.csdn.net/qq51931373/article/details/17008159
）
先说结论：mysql查询数据如果结果集中order by 的字段值相同，例如字段类型为日期精确到天，值都是同一天，返回的结果会按所使用的索引的不同而返回不同的结果集顺序，即使结果集是一样的。

例如：select * from table_a where train_id in() order by date desc limit 20;(explain 用了date索引) 因为date相同，查询结果为id降序排列
和 select * from table_a where id in(上面查询结果的id，顺序不变) limit 20;(explain 用了id索引)，结果为id升序排列

20210818

诡异sql，mysql环境
select max(run_date),route_train from crew_routing group by route_train and deleted=0;
注意 and 后面的筛选条件跟在group by 后面，查询居然不报错，但是只返回了2行数据，如果把and deleted=0 删除或者group by在前面加where 并跟在where后面，则返回多行数据，具体原因还不知道

DECLARE handler_type HANDLER
FOR condition_value [, condition_value] …
statement

handler_type:
CONTINUE
| EXIT
| UNDO

CREATE TABLE test.t (s1 INT, PRIMARY KEY (s1));

DELIMITER //

CREATE PROCEDURE handlerdemo ( )
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘23000’ SET @x2 = 1;
SET @x = 1;
INSERT INTO test.t VALUES (1);
SET @x = 2;
INSERT INTO test.t VALUES (1);
SET @x = 3;
END;
//

DELIMITER ;

CALL handlerdemo( );

SELECT @x;
±-----+
| @x |
±-----+
| 3 |
±-----+

CREATE TABLE t1 (a INT);

DELIMITER //

FOR i IN 1…3
DO
INSERT INTO t1 VALUES (i);
END FOR;
//

DELIMITER ;

SELECT * FROM t1;
±-----+
| a |
±-----+
| 1 |
| 2 |
| 3 |
±-----+

创建序列号
CREATE SEQUENCE s START WITH 100 INCREMENT BY 10;
SHOW CREATE SEQUENCE s\G

获取下一个序列号值

NEXT VALUE FOR sequence_name
or
NEXTVAL(sequence_name)
or in Oracle mode (SQL_MODE=ORACLE)
sequence_name.nextval
获取当前序列号
For retrieving the last value used by the current connection from a sequence use:
PREVIOUS VALUE FOR sequence_name
or
LASTVAL(sequence_name)
or in Oracle mode (SQL_MODE=ORACLE)

sequence_name.currval

For example:
SELECT NEXTVAL(s);
±-----------+
| NEXTVAL(s) |
±-----------+
| 100 |
±-----------+

使用默认序列号
Using Sequences in DEFAULT
MariaDB starting with 10.3.3
Starting from 10.3.3 you can use Sequences in DEFAULT:

create sequence s1;
create table t1 (a int primary key default (next value for s1), b int);
insert into t1 (b) values (1),(2);
select * from t1;
更新序列号：https://mariadb.com/kb/en/alter-sequence/
ALTER SEQUENCE s RESTART 50;
ALTER SEQUENCE [IF EXISTS] sequence_name
[ INCREMENT [ BY | = ] increment ]
[ MINVALUE [=] minvalue | NO MINVALUE | NOMINVALUE ]
[ MAXVALUE [=] maxvalue | NO MAXVALUE | NOMAXVALUE ]
[ START [ WITH | = ] start ] [ CACHE [=] cache ] [ [ NO ] CYCLE ]
[ RESTART [[WITH | =] restart]

JSON格式数据
https://mariadb.com/kb/en/differences-between-json_query-and-json_value/
Differences between JSON_QUERY and JSON_VALUE
MariaDB starting with 10.2.3
JSON functions were added in MariaDB 10.2.3.

The primary difference between the two functions is that JSON_QUERY returns an object or an array, while JSON_VALUE returns a scalar.

Take the following JSON document as an example

SET @json=‘{ “x”: [0,1], “y”: “[0,1]”, “z”: “Monty” }’;
Note that data member “x” is an array, and data members “y” and “z” are strings. The following examples demonstrate the differences between the two functions.


```handlebars

```bash
SELECT JSON_QUERY(@json,'$'), JSON_VALUE(@json,'$');
+--------------------------------------------+-----------------------+ | JSON_QUERY(@json,'$')                      | JSON_VALUE(@json,'$') |
+--------------------------------------------+-----------------------+ | { "x": [0,1], "y": "[0,1]", "z": "Monty" } | NULL                  |
+--------------------------------------------+-----------------------+

SELECT JSON_QUERY(@json,'$.x'), JSON_VALUE(@json,'$.x');
+-------------------------+-------------------------+ | JSON_QUERY(@json,'$.x') | JSON_VALUE(@json,'$.x') |
+-------------------------+-------------------------+ | [0,1]                   | NULL                    |
+-------------------------+-------------------------+

SELECT JSON_QUERY(@json,'$.y'), JSON_VALUE(@json,'$.y');
+-------------------------+-------------------------+ | JSON_QUERY(@json,'$.y') | JSON_VALUE(@json,'$.y') |
+-------------------------+-------------------------+ | NULL                    | [0,1]                   |
+-------------------------+-------------------------+

SELECT JSON_QUERY(@json,'$.z'), JSON_VALUE(@json,'$.z');
+-------------------------+-------------------------+ | JSON_QUERY(@json,'$.z') | JSON_VALUE(@json,'$.z') |
+-------------------------+-------------------------+ | NULL                    | Monty                   |
+-------------------------+-------------------------+

SELECT JSON_QUERY(@json,'$.x[0]'), JSON_VALUE(@json,'$.x[0]');
+----------------------------+----------------------------+ | JSON_QUERY(@json,'$.x[0]') | JSON_VALUE(@json,'$.x[0]') |
+----------------------------+----------------------------+ | NULL                       | 0                          |
+----------------------------+----------------------------+

笔记本电脑笔记

VMWare\centOS8:
用户名:c
密码:C0.

VMWare\centOS7:
用户名:c
密码:C0.

root密码都是C0.

cenos7固定ip设置（可以通过centos7系统页面设置，
通过页面设置发现：不需要设置/etc/sysconfig/network-scripts/ifconf-ens33文件中BOOTPROTO=static，可以保持BOOTPROTO=none）
ip：192.168.119.145
360极速浏览器收藏夹/学习资料/tcp-ip/
https://blog.csdn.net/qq_31946323/article/details/119301363
vm配置完成固定IP后查看
[root@localhost network-scripts]# cat ifcfg-ens33
TYPE=Ethernet
PROXY_METHOD=none
BROWSER_ONLY=no
BOOTPROTO=none
DEFROUTE=yes
IPV4_FAILURE_FATAL=no
IPV6INIT=yes
IPV6_AUTOCONF=yes
IPV6_DEFROUTE=yes
IPV6_FAILURE_FATAL=no
IPV6_ADDR_GEN_MODE=stable-privacy
NAME=ens33
UUID=93913b2f-ed82-49b7-8ad7-8a8cd1c088dc
ONBOOT=yes
IPADDR=192.168.119.145
PREFIX=24
GATEWAY=192.168.119.2

手动修改vm网络为指定ip后，systmectl start network失败，原因未知，最后恢复原来的动态ip设置才成功，页面新增了网卡配置，删除了原来的默认nes33
重要！！！：windows-虚拟网卡vm8的指定ip需要和虚拟机内部指定的静态ip在同一网段且ip不同，之前以为需要相同导致找了一下午原因
笔记：
/root/.ssh/known_hosts

windows无法ping通虚拟机静态ip解决办法：
windows主机网络适配器-vm8配置指定ip(虚拟机设置的固定ip)后，需要先禁用再启用即可
(尽管虚拟机systemctl start network还是失败，但是ifconfig的ip是对的，且虚拟机可以ping通windows主机和百度)

vmware界面点击ssh连接虚拟机时cmd弹窗报错：“系统找不到指定路径”
，解决办法：管理员身份运行vmware-即可

虚拟机设置-高级-禁用‘测通道缓解’

cenos7上面的mariadb
用户名mysql或者root
密码都是C0.

CREATE USER ‘ec_user’@‘localhost’ IDENTIFIED BY ‘C0.’;
GRANT ALL PRIVILEGES ON . to ‘ec_user’@‘%’ IDENTIFIED BY ‘C0.’ WITH GRANT OPTION;
flush privileges;

mariadb安装包在/apps下面，安装路径按官方默认位置/usr/local/mysql
通过cat /etc/init.d/mysql得知，官方建议自定义配置文件位置:/ect/my.conf
配置文件在/etc/init.d/mysql
自行安装过docker和openssl，在/apps下面可以看到
docker images 可以看到官方示例hello-world
docker run hello-world（不可以用后台方式运行，因为hello-world仅仅输出“Hello from Docker!”便退出了）

2：
docker run -dp 8080:8080 java-docker:latest
vm-cmd运行：
curl --request GET
–url http://localhost:8080/actuator/health
–header ‘content-type: application/json’
或者配置完成vm固定ip以及windows的host对vm的对应后运行：
http://vm-centos7:8080/actuator/health

安装mariadb相关

20220618-19周末编辑的内容被覆盖了

回忆起来一个比较重要的东西，windows下重置mysql密码
C:\Program Files\MySQL\MySQL Server 8.0\bin>.\mysqld.exe --skip-grant-tables --console --shared-memory

创建用户

create user ‘mysql’@‘localhost’;
show warnings;–查看db历史异常以及错误，警告等
show grants for [user];
show privilegs;–查看系统允许哪些privilege；
更改mysql、mariadb用户密码的三种方法：
更新root密码
set password for ‘root’@‘localhost’ = PASSWORD(‘C0.’);
update mysql.user set password=password(‘C0.’) where user=‘root’ and host=‘localhost’;
ALTER USER ‘root’@‘localhost’ IDENTIFIED BY ‘123’;（mariadb可能报错，可能只适合mysql）
mysqladmin -u root -p password ‘C0.’(这里user【root】不能写成【‘root’@‘localhost’】,因为他会自动加上【@localhost】)

查询mariabd版本信息
1.show variables like ‘%ersion%’;
或者
2.shell里运行:mysqladmin --version(如果提示没权限ERROR则：mysqladmin -u root -p --version)
或者
mysqlshow --version
my_print_defaults --version
3.help version;
Examples:
mysql> SELECT VERSION();
-> '5.5.29-stand

MariaDB [(none)]> select
±---------------+
| version() |
±---------------+
| 5.5.68-MariaDB |
±---------------+
1 row in set (0.00 sec)

shell>mysqladmin --print-defaults;（如果提示没权限ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: NO)
则如果提示没权限ERROR则：mysqladmin -u root -p --print-defaults）
mysqladmin [-help]可以出现提示
mysqladmin -u root -p variables

查询mysql读取的默认文件
shell>my_print_defaults
shell>mysql --print-defaults

安装mysql

$ scripts/mysql_install_db --user=mysql
–basedir=/opt/mysql/mysql
–datadir=/opt/mysql/mysql/data

mysql初始安全设置
mysql_secure_installation

linux更新mariadb（mysql）-仓库repository
https://mariadb.com/kb/en/yum/#updating-the-mariadb-yum-repository-to-a-new-major-release

查看mysql启动失败原因
mysqld（或者mysql） --help --verbose | grep ‘log-error’ | tail -1
mysqld --help --verbose | grep ‘datadir’ | tail -1

mysql配置文件
https://mariadb.com/kb/en/configuring-mariadb-with-option-files/

增加mysql开机启动
chkconfig --add mysqld

mysql增加user
如果是centos7自带mariadb则，先root非密码登录，然后update mysql.users set passwd=‘C0.’ where user=‘root’;
安装好mysql后，登录mysql，create user ‘mysqluser’@‘%’;
然后 grant all privileges on . to ‘mysqluser’@‘%’ identified by ‘123’ with grant option;

cenos8上面的mariadb
yum localinstall *.rpm
用户名mysql或者root
密码都是C0.
systemctl status mysql可以查看安装位置，配置文件位置等
或者 systemctl cat mysql
安装包位置/usr/local/
通过systemctl cat mysql得知，官方建议自定义配置文件位置:/etc/systemd/system/mariadb.service.d/c.conf
自行安装过git，在/data/apps下面可以看到

20220613 rpm安装mariadb

1.https://mariadb.com/downloads/下载对应版本的rpm包
2.参考tar包里的readme
（2.1.把rmp包放到~/目录下【linux用户目录】
2.2.cd /opt
tar -xf ~/mariadb-10.6.8-rhel-7-x86_64-rpms.tar
mariadb-10.6.8-rhel-7-x86_64-rpms/setup_repository
yum install -y MariaDB-server）
3.linux运行mariadb-secure-installation（运行 mysql_secure_installation: 会显示【未找到命令】）

tar 包安装mariadb 10.5

tar包位置/apps
安装位置： /usr/local/mysql
配置文件：/etc/my.cnf下新增user=mysql 新增baseurl=/usr/local/mysql(mysql 可以为指向安装目录的软连接)
按照mariadb安装位置下/support-files/mysql.server文件内容建议做如下操作：
1.在/etc/my.cnf文件中指明baseurl
2.复制/support-files/mysql.server 到/etc/init.d/下并重命名为mysql
3.创建软连 /etc/rc0.d/K01mysql 和 /etc/rc3.d/S99mysql 指向 /etc/init.d/mysql
4.chkconfig --add mysql(systemctl enable mysql 会重定位到chkconfig)
至此用systemctl start mysql 可以正常启动了

20220621

mariadb安装目录启动mysql
方法1.安装目录/mysqld_safe -uroot -p &
方法2.安装目录/mysqld.server或者/bin/mysql
查询变量：
登录后：show variables;
或者不登录：mysqladmin -uroot -p variables | grep -i sock*
或者 mysqladmin -uroot -p --variable-name=value
shell>my_print_defaults
shell>mysqladmin --print-defaults

mysql_secure_installation(或者mariadb_secure_installation)执行去除匿名登录后仍然可以匿名登录的可能原因之一
GRANT PROXY ON ‘’@‘%’ to ‘root’@‘localhost’ with grant option

REVOKE
priv_type [(column_list)]
[, priv_type [(column_list)]] …
ON [object_type] priv_level
FROM user [, user] …

REVOKE ALL PRIVILEGES, GRANT OPTION
FROM user [, user] …

ALTER USER foo2@test IDENTIFIED BY ‘mariadb’;
ALTER USER ‘monty’@‘localhost’ PASSWORD EXPIRE INTERVAL 120 DAY;
ALTER USER ‘monty’@‘localhost’ PASSWORD EXPIRE NEVER;
ALTER USER ‘monty’@‘localhost’ PASSWORD EXPIRE DEFAULT;

ALTER USER ‘marijn’@‘localhost’ ACCOUNT LOCK;
停止mysql_safe
mysqladmin -uroot -p shutdown

20220622 mariadb官网下载rpm包后

按照包内README内容安装后，如果
[root@localhost mysql]# mysql
ERROR 2002 (HY000): Can’t connect to local server through socket ‘/var/lib/mysql/mysql.sock’ (2)
直接运行mysql报错找不到mysql.sock,一般原因是mysqld服务没有启动，需要先确保systemctl start mysql正常启动才行
如果systemctl start mysql报错如下

220622 14:11:45 mysqld_safe mysqld from pid file /var/lib/mysql/localhost.localdomain.pid ended
220622 14:51:20 mysqld_safe Starting mariadbd daemon with databases from /var/lib/mysql
2022-06-22 14:51:20 0 [Note] /usr/sbin/mariadbd (server 10.6.8-MariaDB) starting as process 3530 ...
2022-06-22 14:51:20 0 [Note] InnoDB: Compressed tables use zlib 1.2.7
2022-06-22 14:51:20 0 [Note] InnoDB: Number of pools: 1
2022-06-22 14:51:20 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2022-06-22 14:51:20 0 [Note] InnoDB: Using Linux native AIO
2022-06-22 14:51:20 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728
2022-06-22 14:51:20 0 [Note] InnoDB: Completed initialization of buffer pool
2022-06-22 14:51:20 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=1599133,1599133
2022-06-22 14:51:20 0 [ERROR] InnoDB: Missing FILE_CHECKPOINT at 1599133 between the checkpoint 1599133 and the end 1599**
145.
2022-06-22 14:51:20 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2022-06-22 14:51:20 0 [Note] InnoDB: Starting shutdown...
2022-06-22 14:51:20 0 [ERROR] Plugin 'InnoDB' init function returned error.
2022-06-22 14:51:20 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2022-06-22 14:51:20 0 [Note] Plugin 'FEEDBACK' is disabled.
2022-06-22 14:51:20 0 [ERROR] Unknown/unsupported storage engine: InnoDB
2022-06-22 14:51:20 0 [ERROR] Aborting

关键是中间的Starting crash recovery from checkpoint LSN=1599133,1599133
2022-06-22 14:51:20 0 [ERROR] InnoDB: Missing FILE_CHECKPOINT at
说明mysql尝试从突然宕机状态中恢复，但是恢复失败了。
解决办法是把mysql-data目录（如/var/lib/mysql）下数据库日志文件(ib_logfile0不需要移动ibdata1和ib_buffer_pool)备份到别的地方或者删除即可