分类 MySQL 下的文章

windows备份mysql脚本

windows下备份mysql数据库脚本，可添加到计划任务中实现定时自动备份。默认存储位置为d:\backup（注：为避免中文乱码，请使用ANSI保存）


    @echo off
    
    :begin
    rem 接收信息
    set input=
    set /p input=请输入数据库名称：
    
    if "%input%" == "" (
        goto begin
    )
    
    echo "正在备份 %input%"
    
    
    set yyyy=%date:~,4%
    set mm=%date:~5,2%
    set dd=%date:~8,2%
    if /i %time:~0,2% lss 10 set hh=0%time:~1,1%
    if /i %time:~0,2% geq 10 set hh=%time:~0,2%
    set mn=%time:~3,2%
    set ss=%time:~6,2%
    set date=%yyyy%%mm%%dd%
    set time=%hh%%mn%%ss%
    set filename=%date%_%time%
    
    ::设置备份目录
    set label=d:\
    set backDir=backup\
    set pwd=123456
    
    
    if exist %label%%backDir% (
        ::echo 目录%label%%backDir%已存在，无需创建
    ) else (
        echo 创建%label%%backDir%
        md %label%%backDir%
    )
    
    ::主要为当前正在开发的数据备份，已上线系统优先依据线上系统数据库
    
    "E:\wamp\bin\mysql\mysql5.7.31\bin\mysqldump">mysqldump --opt -P3306 -u root --password=%pwd% --databases %input% --single-transaction >%label%%backDir%%input%_%filename%.sql
    
    :: more...
    
    echo "完成备份 %input%"
    
    goto begin

mysql表中依据一个字段更新另一个字段

判断字段是否存在值，如果不存在则写入本行中另一个字段值。
update table_name set new_column= case when (new_column=0) then old_column else new_column end
即：如new_column不存在值则更新为old_column

shell脚本批量检测mysql表中是否存在指定字段

批量查询数据库中表是否存在指定字段，批量添加表字段：

#!/bin/bash
#exit
db_con="/usr/local/mysql/bin/mysql -uroot -p123456"
arr=(db1 db2 db3)
for db in ${arr[*]}
do
    ${db_con} -e "select count(*) from information_schema.columns where table_schema = '${db}' and table_name = 'table_name' and column_name = 'column_name';"  >> /data/shell/test.txt
    echo ${db} >> /data/shell/test.txt 
    #${db_con} -e "ALTER TABLE ${db}.gy_com_customer ADD new_column varchar(100) NOT NULL default '';" 2>/dev/null
    echo ${db} " --- done"
done

MYSQL5.7版本sql_mode=only_full_group_by问题

错误信息
`SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #3
of SELECT list is not in GROUP BY clause and contains nonaggregated column
'iicityYii.opportunity_conditions.money' which is not functionally dependent
on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by`

  1. 查看 sql_mode
    select @@global.sql_mode
  2. 去掉 ONLY_FULL_GROUP_BY
    set @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

或者：
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

或者，修改mysql配置文件，新增
[mysqld]
sql-mode=""

MySql 百千万级别数据中随机获取一条或多条记录之RAND()优化

最近所做读书小程序中需从千万条数据中随机抽取一条名言语录展示，常规的select * from famous order by RAND() LIMIT 1;，缺少索引及全表扫描，速度异常慢。
SELECT * FROM famous AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM famous)-(SELECT MIN(id) FROM famous))+(SELECT MIN(id) FROM famous)) AS id) AS t2 WHERE t1.id >= t2.id ORDER BY t1.id LIMIT 1;

lnmp MySQL5.7.18 重置 root 密码

1、修改 /etc/my.cnf，在 [mysqld] 小节下添加一行：skip-grant-tables=1
2、重启 mysqld 服务：systemctl restart mysqld
3、使用 root 用户登录到 mysql -u root
4、切换到mysql数据库，更新 user 表：
update user set authentication_string = password('root'), password_expired = 'N', password_last_changed = now() where user = 'root';
在之前的版本中，密码字段的字段名是 password，5.7版本改为了 authentication_string
5、退出 mysql，编辑 /etc/my.cnf 文件，删除 skip-grant-tables=1 的内容
6、重启 mysqld 服务，再用新密码登录即可

  1. 1
  2. 2
  3. 3
  4. 4