mysql 存储过程中使用事务

DROP PROCEDURE IF EXISTS  repair_order_brokerage_pay;
CREATE PROCEDURE repair_order_brokerage_pay()
  BEGIN
    DECLARE error INTEGER DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET error=1;

    START TRANSACTION;

    /*******order_id=122*******/
    /*修改订单售后服务状态,修改后会在下次定时任务时自动发放*/
    UPDATE `xm_orders` SET `is_service_over`='0' WHERE (`id`='122') LIMIT 1;
    /*已经执行了销量增加,把增加的销量重新减去,新版本代码将会修改这一做法,用户不发奖励将不修改售后状态和销量*/
    UPDATE `xm_goods` SET `sales_volume`=sales_volume-2 WHERE (`id`='17') LIMIT 1;
    UPDATE `xm_goods` SET `sales_volume`=sales_volume-1 WHERE (`id`='16') LIMIT 1;
    UPDATE `xm_goods` SET `sales_volume`=sales_volume-1 WHERE (`id`='14') LIMIT 1;
 
    IF error = 1 THEN
      ROLLBACK;
    ELSE
      COMMIT;
    END IF;
    select error;
  END;

#执行
CALL repair_order_brokerage_pay();

 

mysql使用存储过程重复/批量 插入测试数据

#创建生成测试数据的存储过程
DROP PROCEDURE IF EXISTS test;
CREATE PROCEDURE test() 
BEGIN 
DECLARE i INT DEFAULT 0; 
SET autocommit = 0; 
WHILE i<100000 DO 

INSERT INTO `test`.`user` (`uid`, `task_id`, `publisher_id`, `expired_at`, `update_at`) 
VALUES ( 450+i, '125', '1', '2','2017-09-08 14:42:23', '2017-09-08 13:42:30');

SET i = i+1; 
IF i%1000 = 0 THEN 
COMMIT; 
END IF; 
END WHILE; 
END;

#执行存储过程生成测试数据
CALL test();

 

开启mysql执行日志

在mysql命令行或者客户端管理工具中执行:SHOW VARIABLES LIKE “general_log%”;

结果:

general_log OFF
general_log_file /var/lib/mysql/localhost.log

OFF说明没有开启日志记录

分别执行开启日志以及日志路径和日志文件名

SET GLOBAL general_log_file = ‘/var/lib/mysql/localhost.log’;
SET GLOBAL general_log = ‘ON’;

还要注意

这时执行的所有sql都会别记录下来,方便查看,但是如果重启mysql就会停止记录需要重新设置

SHOW VARIABLES LIKE “log_output%”;

如果是NONE,需要设置

SET GLOBAL log_output=’TABLE,FILE’

mysql查询字段重复和删除重复字段

/*查询所有uid 重复的条目*/
SELECT * FROM xm_user_referee_verify where uid in (SELECT uid FROM xm_user_referee_verify GROUP BY uid HAVING COUNT(uid)>1) ORDER BY uid;

/*查询所有 referee_secondly_id 重复的条目*/
SELECT * FROM xm_user_referee_verify where referee_secondly_id in (SELECT referee_secondly_id FROM xm_user_referee_verify GROUP BY referee_secondly_id HAVING COUNT(referee_secondly_id)>1) ORDER BY referee_secondly_id;

/*查询 */
SELECT * FROM xm_user_referee_verify WHERE (uid,referee_secondly_id) in (SELECT uid,referee_secondly_id FROM xm_user_referee_verify GROUP BY uid,referee_secondly_id HAVING count(*)>1)

/*给raise_uid 字段设值*/
UPDATE `xm_user_referee_verify` SET `raise_uid`=`referee_secondly_id` WHERE raise_uid=0 OR raise_uid IS NULL

myql 批量生成替换表前缀sql语句

1.查询所有表

select table_name FROM information_schema.tables;

2.查询指定前缀的表

select table_name FROM information_schema.tables where table_name like ‘go_%’;

3.生成从指定位置开始替换表名的sql语句(”3″表示从”go_”后面开始)

Select CONCAT( ‘ALTER TABLE ‘, table_name, ‘ RENAME TO xm_’, substring(table_name,3),’;’ )FROM information_schema.tables Where table_name LIKE ‘go_%’;

 

将生成的重命名语句语句复制出来,拿去执行,OK

mysql 数据库升级工具使用

对于mysql数据库管理来说,一般我们使用的是navicat,当然了其他就不说了,现在我们来介绍一个新的工具:SQLyog(https://www.webyog.com/)

我们使用navicat导出的sql文件总是很大,而使用SQLyog导出的sql文件只有navicat导出的文件的1/3到1/2而已,所以SQLyog首先就适合导出作为安装的sql语句(当然我们也不会使用navicat导出的 文件来作为安装文件使用)。

我们在修改测试版的时候数据库结构总是在零零碎碎的变动,当期们想要将生产环境升级的时候,问题来了,要怎么记住每一次改动呢?

我要介绍的就是SQLyog中的一个数据升级工具,能完全解决这个问题。

打开SQLyog,选择 高级工具->架构同步工具

左边选择最新版本数据库结构,右边选择旧的数据库结构,点击比较,就能生成升级数据库用语句,方便吧

 

 

CentOS下mysql 设置远程连接

[root@iZwz94arwsjf9juf1lfjrgZ ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 31
Server version: 5.7.11-log Source distribution

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql;
Database changed
mysql> update user set host='%' where user='root';
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit;

 

关于设置mysql_mode的一些问题

安装lnmp1.3完成后使用SELECT @@sql_MODE 查询sql_mode得出的结果为空,因为程序需要
把sql_mode 设置为sql_mode = ‘NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’,
因此在sql中执行

SET sql_mode=`NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION`;
SET global sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

 

再执行
SELECT @@sql_MODE;

得出结果为NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION ,大喜,以为成功了,遂重启sql,再试一下SELECT @@sql_MODE ,得出的结果为空,悲,不生效!!!!
不服
在  /etc/my.cnf  最后添加一行
sql_mode=”NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”

重启sql,SELECT @@sql_MODE 出来的结果还是空,不生效!!

后来将sql_mode=”NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION” 放到my.cnf [mysqld]中,才解决了问题!!!

记录下来,让以后不再踩坑!