mysql – IT漫步

Use regex to match and replace in MySql 8.0+ or MariaDB

2021年10月22日2021年10月22日 by ylhyh

UPDATE server_list_server
  SET world_id=regexp_replace(ip,'wg(\\d+)\.glwsy\.szgla\.com;?','\\1')
  WHERE ip REGEXP 'wg(\\d+)\.glwsy\.szgla\.com;?'
    AND world_id!=regexp_replace(ip,'wg(\\d+)\.glwsy\.szgla\.com;?','\\1');

REGEXP
regexp_replace
\\1 is the placeholder for matched group

Change value for a PK column with AUTO_INCREMENT in mysql

2020年9月30日 by ylhyh

show create table TABLE_NAME; // Check current AUTO_INCREMENT value
update IGNORE TABLE_NAME set primary_field = 'value' ...; // Change the column value with IGNORE keyword
alter table TABLE_NAME AUTO_INCREMENT = 50; // Set AUTO_INCREMENT
show create table TABLE_NAME;  // Check current AUTO_INCREMENT value again