Mysql 常见DML sql 总结

单条语句批量修改索引

ALTER TABLE `basic_self_lifting_point` DROP INDEX idx_company_code,
ADD UNIQUE `idx_company_id_slp_code` ( `company_id`, `code` ) USING BTREE COMMENT '根据货主Id+自提点code做唯一键',
ADD UNIQUE `idx_company_code_slp_code` ( `company_code`, `code` ) USING BTREE COMMENT '根据货主Code+自提点code做唯一键';

批量新增字段（不可指定添加字段的位置）

ALTER TABLE basic_sku_detail ADD (
    period_of_forbid_sale INT ( 4 ) NOT NULL DEFAULT '0' COMMENT '禁售天数',
    period_of_forbid_take INT ( 4 ) NOT NULL DEFAULT '0' COMMENT '禁入天数',
    sku_lot_attrs json DEFAULT NULL COMMENT '商品批次属性列表',
    extend_info json DEFAULT NULL COMMENT '扩展字段' ,
    `sku_classify4` VARCHAR ( 64 ) NOT NULL DEFAULT '' COMMENT '商品四级分类',
    `sku_classify5` VARCHAR ( 64 ) NOT NULL DEFAULT '' COMMENT '商品五级分类',
    `sku_classify6` VARCHAR ( 64 ) NOT NULL DEFAULT '' COMMENT '商品六级分类',
    `sku_classify_name1` VARCHAR ( 64 ) NOT NULL DEFAULT '' COMMENT '商品一级分类名称',
    `sku_classify_name2` VARCHAR ( 64 ) NOT NULL DEFAULT '' COMMENT '商品二级分类名称',
    `sku_classify_name3` VARCHAR ( 64 ) NOT NULL DEFAULT '' COMMENT '商品三级分类名称',
    `sku_classify_name4` VARCHAR ( 64 ) NOT NULL DEFAULT '' COMMENT '商品四级分类名称',
    `sku_classify_name5` VARCHAR ( 64 ) NOT NULL DEFAULT '' COMMENT '商品五级分类名称',
    `sku_classify_name6` VARCHAR ( 64 ) NOT NULL DEFAULT '' COMMENT '商品六级分类名称' 
);

单个新增字段（可指定位置）

ALTER TABLE conso_order_list ADD `version` int(11) NOT NULL DEFAULT '0' COMMENT '版本号，用于乐观锁更新记录' after payment_detail;

批量修改字段定义

ALTER TABLE basic_warehouse MODIFY `phone_num` varchar(1024) NOT NULL DEFAULT '' COMMENT '联系人手机（加密存储）',
MODIFY  `tel_num` varchar(1024) NOT NULL DEFAULT '' COMMENT '联系人电话（加密存储）',
MODIFY  `email` varchar(1024) NOT NULL DEFAULT '' COMMENT '邮箱地址（加密存储）';

查询同一张表里字段相同的数据

		SELECT
		DISTINCT(t2.erp_order_no),
	t2.company_code,
	t2.warehouse_code,
	t2.outbound_order_id,
	t2.trade_order,
	t2.shop_name,
	t2.receiver_province,
	t2.receiver_city,
	t2.receiver_area,
	t2.receiver_province,
	t2.receiver_info ->> "$.receiver_name",
	t2.receiver_info ->> "$.receiver_address",
	t2.receiver_info ->> "$.receiver_phone",
	t2.create_time 
FROM
	(
	SELECT
		company_code,
		warehouse_code,
		erp_order_no,
		outbound_order_id,
		trade_order,
		shop_name,
		receiver_province,
		receiver_city,
		receiver_area,
		receiver_info,
		create_time 
	FROM
		outbound_order_list 
			WHERE
		create_time BETWEEN "2022-10-25 10:00:00" 
		AND "2022-10-25 22:00:00" 
		AND order_status != 10000 
		GROUP BY 
receiver_province,receiver_city,receiver_area,receiver_info->>"$.receiver_name",
receiver_info->>"$.receiver_phone",receiver_info->>"$.receiver_address"
HAVING COUNT(*) > 1
	) AS t1
	LEFT JOIN outbound_order_list AS t2 
	ON t1.company_code = t2.company_code 
	AND t1.receiver_province = t2.receiver_province 
	AND t1.receiver_city = t2.receiver_city 
	AND t1.receiver_area = t2.receiver_area 
	AND t1.receiver_info ->> "$.receiver_name" = t2.receiver_info ->> "$.receiver_name" 
	AND t1.receiver_info ->> "$.receiver_phone" = t2.receiver_info ->> "$.receiver_phone" 
	AND t1.receiver_info ->> "$.receiver_address" = t2.receiver_info ->> "$.receiver_address"
	WHERE
		t2.create_time BETWEEN "2022-10-25 10:00:00" 
		AND "2022-10-25 22:00:00" 
		AND t2.order_status != 10000 

MP更新Json字段

public void update(List<Long> ids, Integer batchCounts) throws InterruptedException {
        List<List<Long>> partition = Lists.partition(ids, batchCounts);
        for (List<Long> strings : partition) {
            UpdateWrapper<BasicSkuDetailDAO> updateWrapper = new UpdateWrapper<>();
            List<String> cpCode = Arrays.asList("G11", "G18");
            updateWrapper.setSql("medicine_info = JSON_REPLACE( medicine_info, \"$.product_category\", \"v0\" ) ");
            updateWrapper.lambda().in(BasicSkuDetailDAO::getSkuId, strings)
                    .in(BasicSkuDetailDAO::getCompanyCode, cpCode);
            updateWrapper.eq("medicine_info -> \"$.product_category\"", "非药品");
            basicSkuDetailMapper.update(null, updateWrapper);
            Thread.sleep(batchCounts);
        }
        Thread.sleep(4000);
        for (List<Long> longs : partition) {
            sendBatchCmd(longs, "系统", SkuConst.SKU_ISSUE_ACTION, false);
        }
    }

添加虚拟列

alter table inbound_order_list add `customer_erp_no` varchar(128) GENERATED ALWAYS AS (ifnull(json_unquote(json_extract(`extra_info`,'$.customer_erp_no')),'')) VIRTUAL COMMENT '客户ERP单号';

动态更新Json字段

updateWrapper.setSql(!StringUtil.empty(userDef.getUserDef1()), "user_def = json_set(user_def, '$.user_def1', " + userDef.getUserDef1() + ")");

把Mysql数据生成Json

SELECT JSON_OBJECT('id',id
            'name',name,
            'score',score) as json FROM order_list where id = 477152;

## {"id": 1, "name": "数学",score": "95.5"}