Skip to main content

2 posts tagged with "mysql"

View All Tags

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

· One min read
<code class="sql">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

· One min read
<code class="sql">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
ClustrMaps