mysql常用语句模板

添加字段

#指定在哪个字段后面添加新字段
alter table table1 Add column 列名1 VARCHAR(字段长度) DEFAULT NULL COMMENT '字段描述' AFTER  列名3;

#不指定在哪个字段后面添加新字段
alter table table2 Add column 列名2 VARCHAR(字段长度) DEFAULT NULL COMMENT '字段描述';

添加索引

#添加普通索引
alter table tablename add index indexname (columnlist) ;

#删除索引
alter table tablename drop index indexname ;

建表sql模板

DROP TABLE IF EXISTS common_process_log;
CREATE TABLE `common_process_log`
(
    `id`                BIGINT(20)  NOT NULL AUTO_INCREMENT COMMENT 'ID',
    `business_id`       VARCHAR(64) NOT NULL COMMENT '业务id',
    `business_ext_id`   VARCHAR(32)          DEFAULT NULL COMMENT '扩展业务id',
    `batch_id`          VARCHAR(32)          DEFAULT NULL COMMENT '针对批量处理记录批次号',
    `business_type`     VARCHAR(32) NOT NULL COMMENT '业务类型',
    `idempotent_id`     VARCHAR(32) NOT NULL COMMENT '幂等参数 唯一索引保证幂等',
    `content`           TEXT        NOT NULL COMMENT '内容',
    `process_count`     INT(11)     NOT NULL DEFAULT '1' COMMENT '处理次数',
    `delay_second`      INT(11)     NOT NULL DEFAULT '0' COMMENT '延迟补偿时间，定义任务扫描处理时间',
    `max_process_count` INT(11)     NOT NULL DEFAULT '0' COMMENT '最大处理次数',
    `state`             INT(2)               DEFAULT NULL COMMENT '推送状态 -2推送失败不参与后续补偿 -1_处理失败 0_待处理 1_处理成功 ',
    `user_id`           BIGINT(20)           DEFAULT NULL COMMENT '操作人id',
    `user_name`         VARCHAR(32)          DEFAULT NULL COMMENT '操作人',
    `created_at`        DATETIME             DEFAULT NULL COMMENT '创建时间',
    `updated_at`        DATETIME             DEFAULT NULL COMMENT '最后一次处理时间',
    `trace_id`          VARCHAR(32) COMMENT '日志的traceId 通过它可以去日志系统获取相应的关联日志',
    PRIMARY KEY (`id`),
    UNIQUE KEY `ix_common_process_log_idempotent_id` (`idempotent_id`),
    KEY `ix_common_process_log_batch_id` (`batch_id`),
    KEY `ix_common_process_log_business_id` (`business_id`),
    KEY `ix_common_process_log_business_ext_id` (`business_ext_id`),
    KEY `ix_common_process_log_created_at` (`created_at`),
    KEY `ix_common_process_log_updated_at` (`updated_at`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 6
  DEFAULT CHARSET = utf8
  ROW_FORMAT = DYNAMIC COMMENT ='公共的处理日志';

查看表结构

1.查看表columns信息

show full columns from module_talk_chat;
show full columns from module_talk_chat_log_metric;

2.查看表信息

show table status  like 'ent_remote'

 

 

 根据in id顺序排序

SELECT * from product WHERE id in (108821,108819,108820) order by field(id,108821,108819,108820)

自动修改修改时间

/* 请确认以下SQL符合您的变更需求，务必确认无误后再提交执行 */

ALTER TABLE `tab'ke`
MODIFY COLUMN `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '平台创建COMMENT' 
;

刷数据相关

例子1

给超级管理员新增指定权限

SERT IGNORE ent_rbac_group_permission (groupid, targetid, targettype, allow)
SELECT e.id, 'batchdeploy::managePackages', 1, 1 FROM ent_rbac_group e
    WHERE  NOT EXISTS (
        SELECT 1 FROM ent_rbac_group_permission ergp WHERE ergp.groupid = e.id AND  ergp.targetid = 'batchdeploy::managePackages'
    );

例子2

INSERT IGNORE ent_rbac_group_permission (groupid, targetid, targettype, allow)
SELECT e.id, 'batchdeploy::managePackages', 1, 1 FROM ent_rbac_group e
    WHERE  NOT EXISTS (
        SELECT 1 FROM ent_rbac_group_permission ergp WHERE ergp.groupid = e.id AND  ergp.targetid = 'batchdeploy::managePackages'
    );

 

给所有角色新增指定权限

 

例子3

-- 请导出id 用于清缓存
select e.id from ent_rbac_group e where
 not exists(select p.`groupid` from ent_rbac_group_permission p where e.id=p.`groupid` and  p.`targetid`='remote::DeleteRemote') and ( `name` ='普通管理员');

select e.id from ent_rbac_group e where
 not exists(select p.`groupid` from ent_rbac_group_permission p where e.id=p.`groupid` and  p.`targetid`='remote::TagManager') and ( `name` ='普通管理员');


-- 新增权限默认需要超级管理员和普通管理员拥有,线上发现部分角色没刷成功 需要手动刷新
INSERT IGNORE INTO ent_rbac_group_permission (groupid, targetid, targettype, allow)
select e.id, 'remote::DeleteRemote', 1, 1 from ent_rbac_group e where
 not exists(select p.`groupid` from ent_rbac_group_permission p where e.id=p.`groupid` and  p.`targetid`='remote::DeleteRemote') and ( `name` ='普通管理员');


INSERT IGNORE INTO ent_rbac_group_permission (groupid, targetid, targettype, allow)
select e.id, 'remote::TagManager', 1, 1 from ent_rbac_group e where
 not exists(select p.`groupid` from ent_rbac_group_permission p where e.id=p.`groupid` and  p.`targetid`='remote::TagManager') and ( `name` ='普通管理员');

例子4

jion修改数据

UPDATE cold_chain_alarm_order o
JOIN cold_chain_alarm_order_report c ON c.alarm_order_no = o.alarm_order_no
SET o.last_report_time = c.created_time
WHERE o.order_status = 20 AND o.last_report_time IS NULL;