# MySql常用命令

# mysql锁超时

# 事务未提交
SELECT * FROM information_schema.INNODB_TRX;
# 通过返回的trx_mysql_thread_id 查询,host是服务器和端口
SELECT
    ID,
    USER,
    HOST,
    DB,
    COMMAND,
    TIME,
    STATE,
    INFO
FROM information_schema.PROCESSLIST
WHERE ID = 2923270;

    
SELECT
    r.trx_id AS 等待事务ID,
    r.trx_mysql_thread_id AS 等待线程ID,
    r.trx_query AS 等待SQL,
    b.trx_id AS 阻塞事务ID,
    b.trx_mysql_thread_id AS 阻塞线程ID,
    b.trx_query AS 阻塞SQL,
    TIMESTAMPDIFF(SECOND, b.trx_started, NOW()) AS 阻塞时间_秒,
    l.lock_table AS 锁表,
    l.lock_index AS 锁索引,
    l.lock_type AS 锁类型,
    l.lock_mode AS 锁模式,
    -- 生成批量 KILL 命令（阻塞时间超过阀值才包含）
    GROUP_CONCAT(
            CASE
                WHEN TIMESTAMPDIFF(SECOND, b.trx_started, NOW()) > 1
                    THEN CONCAT('KILL ', b.trx_mysql_thread_id, ';')
                ELSE NULL
                END
                SEPARATOR ' '
        ) AS 批量Kill命令
FROM information_schema.innodb_lock_waits w
         JOIN information_schema.innodb_trx r
              ON w.requesting_trx_id = r.trx_id
         JOIN information_schema.innodb_trx b
              ON w.blocking_trx_id = b.trx_id
         JOIN information_schema.innodb_locks l
              ON w.requested_lock_id = l.lock_id
GROUP BY b.trx_id, r.trx_id, l.lock_table, l.lock_index, l.lock_type, l.lock_mode;

-- 批量修改
UPDATE table1 t1 INNER JOIN table2 t2 
ON t1.id = t2.id
SET t1.update_time = t2.update_time;


-- 在test_table 表的 order_type 字段之后，新增一个字段 order_status，设置对应的类型，长度，是否为null，默认值，注释
ALTER TABLE test_table ADD COLUMN `order_status` tinyint(2) NOT NULL DEFAULT '0' COMMENT '订单状态 0未支付 1已支付' AFTER `order_type`;

ALTER TABLE pms_product ADD COLUMN `album_video` varchar(255) DEFAULT NULL COMMENT '轮播视频' AFTER `album_pics`;

-- 修改一个字段的类型
ALTER TABLE test_table MODIFY test_value VARCHAR(10) NOT NULL DEFAULT '' COMMENT '字段注释';

-- 修改一个字段的名称，此时要重新指定该字段的类型
ALTER TABLE test_table CHANGE test_value_old test_value_new VARCHAR(10) NOT NULL DEFAULT '' COMMENT '字段注释';

alter table pms_product_evaluation modify `evaluation_content` varchar(255) DEFAULT '' COMMENT '评价内容';

-- 删除test_table表的 test_value字段 
ALTER TABLE test_table DROP COLUMN test_value;

--首页轮播广告表 sms_home_advertise
ALTER TABLE sms_home_advertise ADD COLUMN created_by varchar(64) DEFAULT NULL COMMENT '创建人';
ALTER TABLE sms_home_advertise ADD COLUMN created_time datetime DEFAULT NULL COMMENT '创建时间';
ALTER TABLE sms_home_advertise ADD COLUMN updated_by varchar(64) DEFAULT NULL COMMENT '修改人';
ALTER TABLE sms_home_advertise ADD COLUMN updated_time datetime DEFAULT NULL COMMENT '修改时间';
ALTER TABLE sms_home_advertise ADD COLUMN is_del int(1) DEFAULT '0' COMMENT '0：未删除，1：已删除';

-- 添加索引
ALTER TABLE `kxx_pro`.`oms_order_product` ADD INDEX `idx_productid` (`express_number`, `order_state`, `order_id`, `product_id`);

mysql 保留两位小数

https://www.cnblogs.com/xiaomai333/p/7647381.html

函数

TRUNCATE 直接截取，不四舍五入

https://www.cnblogs.com/miracle-luna/p/12041781.html

floor 返回小于等于该值的最大整数

select floor(1.23),floor(-1.23);

group_concat 拼接字符串

https://www.cnblogs.com/llq1214/p/11202866.html

group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )

varchar字符串默认：utf8mb4_general_ci 区分大小写：utf8mb4_bin

# mysql服务器操作命令

# 查询库
show databases;
# 使用库
use jumpserver;
# 查询表
show tables;

BigDecimal.ROUND_HALF_UP
ROUND_DOWN

RoundingMode.HALF_EVEN

拼接
SELECT GROUP_CONCAT(COLUMN_NAME SEPARATOR ",") FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'kxx_yb_dev' AND TABLE_NAME = 'ums_lottery_win_award_record';

列转行
select GROUP_CONCAT(id,"|") from  yb_giveaways_trans_his;

去掉GROUP_CONCAT拼接中的逗号，使用 SEPARATOR
select GROUP_CONCAT(id SEPARATOR '|') from  yb_giveaways_trans_his;

mysql like多个值
https://blog.csdn.net/forypt/article/details/105676763
模糊匹配集合 REGEXP
SELECT id FROM yb_giveaways_buy_his WHERE related_id  REGEXP 'id":"1238|id":"1288' and stat='2' ;

← MySql sql_01脚本 →