分类： 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();

 

SELECT COLUMN_NAME FROM information_schema.`COLUMNS` WHERE `TABLE_NAME` = ‘table_name’ AND `TABLE_SCHEMA` = ‘db_name’ LIMIT 0, 1000

#创建生成测试数据的存储过程
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命令行或者客户端管理工具中执行：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’

/*查询所有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

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

[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;

 

UPDATE 66_help SET `content`=REPLACE(`content`, '商品', '预约');

 

安装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]中,才解决了问题！！！

记录下来，让以后不再踩坑！