mysql常用操作汇总

mysq查询结果，用逗号分割

SET SESSION group_concat_max_len = @@max_allowed_packet;  （分割不限制长度）

SELECT GROUP_CONCAT(vehicle SEPARATOR ',') AS separated_vehicles
FROM `tms_scheduling_car_test`
where `status` = 0
and error_message like '%当前账号下不存在%';

1.自定义mysql的时间戳

ALTER TABLE csp_goods_publish ADD COLUMN create_by varchar(40) DEFAULT NULL COMMENT '创建人';
ALTER TABLE csp_goods_publish ADD COLUMN create_time datetime    default CURRENT_TIMESTAMP null comment '创建时间';
ALTER TABLE csp_goods_publish ADD COLUMN update_by varchar(40) DEFAULT NULL COMMENT '修改人';
ALTER TABLE csp_goods_publish ADD COLUMN update_time datetime    default CURRENT_TIMESTAMP null comment '修改时间';

2.新增字段

ALTER TABLE `csc_claim_settlement_work` ADD COLUMN `claim_status` tinyint(1) NULL DEFAULT NULL COMMENT '赔付状态 0未赔付，1已赔付';

修改字段类型

ALTER TABLE csc_claim_settlement_work MODIFY reply_remark text DEFAULT NULL COMMENT ‘解决方案答复’;

ALTER TABLE bms_ysinvoice_apply MODIFY car_no varchar(100) DEFAULT NULL COMMENT ‘运输工具牌号’;

设置主键自增长id

alter table csp_store_title modify id bigint auto_increment;

查看索引：

SHOW INDEX FROM csp_store_title;

添加索引

create index completion_time_index on dispatch_sheet_monitoring (completion_time) COMMENT '调度单完成时间索引';

添加联合索引

ALTER TABLE users ADD INDEX idx_name (first_name, last_name);

删除索引：

ALTER TABLE bms_yfstock_codeinfo DROP INDEX index_del_flag;

锁表的排查

select * from information_schema.processlist 
where USER = 'jpomstest'
and DB = 'jp_oms';
kill 4885674;

查询死锁的线程

SELECT * 
FROM information_schema.innodb_trx;