mysql常用脚本

偏执的太偏执、 2024-02-19 17:26 48阅读 0赞

> 持续更新

#### mysql查看事务 ####

SELECT
        a.trx_id,
        a.trx_state,
        a.trx_started,
        a.trx_query,
        a.trx_weight,
        a.trx_lock_memory_bytes,
        b.ID,
        b.USER,
        b.DB,
        b.COMMAND,
        b.TIME,
        b.STATE,
        b.INFO,
        c.PROCESSLIST_USER,
        c.PROCESSLIST_HOST,
        c.PROCESSLIST_DB,
        d.SQL_TEXT
    FROM
        information_schema.INNODB_TRX a
    LEFT JOIN
        information_schema.PROCESSLIST b
    ON
        a.trx_mysql_thread_id = b.id
    LEFT JOIN
        PERFORMANCE_SCHEMA.threads c
    ON
        b.id = c.PROCESSLIST_ID
    LEFT JOIN
        PERFORMANCE_SCHEMA.events_statements_current d
    ON
        d.THREAD_ID = c.THREAD_ID;

#### mysql开窗函数实现 ####

求每个部门收入最高的员工

SELECT
        *
    FROM
        (
            SELECT
                IF(@v_department_id=c.department_id,@rn:=@rn+1,@rn:=1) AS rn,
                @v_department_id:=                                        c.department_id,
                c.department_id,
                c.name,
                c.salary
            FROM
                (
                    SELECT
                        b.DEPARTMENT_ID,
                        b.name,
                        SUM(b.SALARY) SALARY
                    FROM
                        department a,
                        salary b
                    WHERE
                        a.id = b.department_id
                    GROUP BY
                        b.DEPARTMENT_ID,
                        b.name) c, (select @v_department_id:=-1) d
            ORDER BY
                c.department_id,
                c.salary DESC) d
    WHERE
        d.rn=1;
    //试验数据
    CREATE TABLE department (id int, name varchar(100)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    CREATE TABLE salary (department_id int, name varchar(100), salary int) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    INSERT INTO department (id, name) VALUES (1, '霸气的部门');
    INSERT INTO department (id, name) VALUES (2, '牛皮的部门');
    INSERT INTO department (id, name) VALUES (3, '最拽的部门');
    INSERT INTO salary (department_id, name, salary) VALUES (1, 'Superman', 1000);
    INSERT INTO salary (department_id, name, salary) VALUES (1, 'Superman', 2000);
    INSERT INTO salary (department_id, name, salary) VALUES (1, 'spiderman', 3500);
    INSERT INTO salary (department_id, name, salary) VALUES (1, 'hulk', 6000);
    INSERT INTO salary (department_id, name, salary) VALUES (2, 'Jack', 5000);
    INSERT INTO salary (department_id, name, salary) VALUES (2, 'Rose', 3000);
    INSERT INTO salary (department_id, name, salary) VALUES (2, 'Tom', 4000);
    INSERT INTO salary (department_id, name, salary) VALUES (3, 'Jerry', 2000);
    INSERT INTO salary (department_id, name, salary) VALUES (3, 'Harry', 3000);

#### mysql查看配置参数 ####

show variables like '%buffer%';

#### mysql查看当前正在使用的表 ####

show open tables;

#### mysql查看引擎状态（包含最后一次死锁原因记录） ####

show engine innodb status

#### mysql查看当前进程 ####

show processlist;（进程对应的sql可能显示不全）
    show full processlist;（进程对应的sql可以显示全，内容可能会贼多）

#### mysql查看执行计划 ####

explain select * from tableName;
    desc select * from tableName;

#### mysql导出结构不包含数据 ####

// 如果不指定表则代表导出整个库，表可以指定多个
    mysqldump --skip-lock-table --no-data -hip -uuser -ppwd -Pport dbname tableName > dump.sql

#### mysql导出数据不包含结构 ####

mysqldump --skip-lock-table --no-create-info --no-create-db -hip -uuser -ppwd -Pport dbname tableName > dump.sql

#### mysql查看建表语句 ####

show create table filter_monitor

#### mysql创建、删除、查看索引 ####

// 创建
    ALTER TABLE table_name ADD INDEX index_name (column_list);
    ALTER TABLE table_name ADD UNIQUE (column_list);
    ALTER TABLE table_name ADD PRIMARY KEY (column_list);
    CREATE INDEX index_name ON table_name (column_list);
    CREATE UNIQUE INDEX index_name ON table_name (column_list);
    CREATE TABLE `pack_group` (
      PRIMARY KEY (`id`),
      UNIQUE `idx_shop_beehive_packable_time` (`shop_id`,`beehive_id`,`packable_time`),
      KEY `idx_create_time` (`create_time`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='分组状态表';
    // 删除
    DROP INDEX index_name ON talbe_name;
    ALTER TABLE table_name DROP INDEX index_name;
    ALTER TABLE table_name DROP PRIMARY KEY;
    // 查看
    show index from tblname;
    show keys from tblname;

#### mysql查看表碎片 ####

如果索引的Cardinality属性与count(distinct字段）差别很大，说明碎片很多，需要整理，不然执行可能会很慢

select count(distinct columnName) from tableName;
    show index from tableName;

#### mysql innodb引擎数据碎片整理 ####

// 查看表的磁盘碎片，如果很多，则进行收集整理
    alter table tableName engine=innodb;
    analyze table tableName;(不会立即整理，会后台慢慢整理，不会阻塞当前会话）
    optimize table tableName;(立即整理，阻塞当前会话，不可终止，如果终止可能会导致表数据文件损坏）

#### mysql 更改字段 ####

alter table tableName modify `columnName` tinyint NOT NULL COMMENT '注释', modify ...;
    ALTER TABLE tableName CHANGE oldColumnName newColumnName CHAR(32) NOT NULL DEFAULT '123';

#### mysql查看端口与版本号 ####

show global variables like 'port';
    show global variables like 'version';

#### mysql查看事务隔离级别 ####

select @@global.tx_isolation;

#### 清理binlog ####

# 指定某个binlog之前的日志删除
    purge binary logs to 'binlog.000068';
    # 按照时间删除binlog
    purge binary logs before '2023-06-16 00:00:00';

#### 重启服务 ####

sudo /usr/local/mysql/support-files/mysql.server start
    sudo /usr/local/mysql/support-files/mysql.server stop
    sudo /usr/local/mysql/support-files/mysql.server restart

#### 修改root密码 ####

# 正常模式下
    ALTER USER 'root'@'localhost' IDENTIFIED BY '哈哈';
    # skip-grant-tables 模式下报错
    ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
    # 先执行
    update user set authentication_string='' where user='root';
    flush privileges;
    # 停止mysql服务，取消skip-grant-tables配置，启动mysql服务后，root可以无密码登陆
    # root登陆后修改root密码
    ALTER USER 'root'@'%' IDENTIFIED BY 'haha';
    flush privileges;