Mysql特殊用法分享

不存在则插入，存在则更新的2种写法

前置使用条件，必须有唯一索引

-- 1 REPLACE INTO
REPLACE INTO typora.ip_view_times_record (ip, view_times, url) VALUES('10.25.130.64', 1, 'https://10.25.168.80/fhh/index.html?url=index.md543');

-- 2 ON DUPLICATE key update
-- 当更新的是索引时，容易发生死锁。注意全面测试，这种方式适合数据大批量操作
INSERT INTO typora.ip_view_times_record (ip, url) VALUES('10.25.130.64', 'https://10.25.168.80/fhh/index.html?url=index.md11111')
ON DUPLICATE key update view_times = if(view_times, 0) + 1 ;

-- 3 先更新，更新为0，则插入。分段sql中间存在较低的时差，分析其影响后再使用
if(updateRow > 1){
	insert();
}

前2种的差异：

REPLACE INTO 每次会把这条数据先清除，在重新插入values中的值，所以除了一次会更新1条数据，后面会更新2条数据；

INSERT INTO ON DUPLICATE key update 原理是先查，如果没有再插入，否则更新；

批量更新策略

-- 1. 适用于不同更新条件更新不同内容，性能取决于查询性能，大多数情况性能表现较好，一般用作数据更新、插入
INSERT INTO table (ip, url) VALUES('10.25.130.64', 'https://10.25.168.80/fhh/index.html?url=index.md11111')
ON DUPLICATE key update xxx;

-- 2. 适用于不同更新条件更新不同内容，性能取决于查询性能，大多数情况性能表现较好（小表在右）
with tmp as (
    select name, stat_date from  user1 where stat_date >= '2024-05-21'
)
, tmp1 as (
    select a.name, b.stat_date from user a inner join tmp b on a.name = b.name
)
update user a INNER JOIN tmp1 b on a.name = b.name set a.stat_date = b.stat_date;

-- 2.1
update test a INNER JOIN test1 b on a.account = b.account set a.account1 = b.account1;

-- 3. 性能一般，适用于不同条件 更新同一内容
update a set a.col1 = xxx where a.col2 in (....)

-- 4. 性能较好，exists 适用于判断存在性。
update user b set stat_date = '2024-05-21' where exists (select 1 from user1 a where a.name = b.name and stat_date >= '2024-05-21');

可重复执行DDL

drop table if exists crr_policy_index;

建索引

set @v_count = 0;
select count(*) into @v_count from information_schema.statistics where TABLE_SCHEMA='fhh' and TABLE_NAME='fhh_test' and INDEX_NAME = 'fhh_test_account_idx';
set @sql = if(@v_count = 0, "CREATE INDEX fhh_test_account_idx USING BTREE ON fhh.fhh_test (account, account1)", "select 'fhh_test.fhh_test_account_idx is OK.'");
prepare stmt from @sql;
execute stmt;

其他使用if exists关键字，或参考建索引（当不支持 if exists 关键字时）