Replace string in column value MYSQL

Question

Hi i have a table named data_table with columns ID | DATA

Id is integer and Data stored like this:

a:19:{s:10:"store_name";s:9:"STORENAME";s:6:"social";a:7:{s:2:"fb";s:0:"";s:7:"twitter";s:0:"";s:9:"pinterest";s:0:"";s:8:"linkedin";s:0:"";s:7:"youtube";s:0:"";s:9:"instagram";s:0:"";s:6:"flickr";s:0:"";}s:7:"payment";a:2:{s:6:"paypal";a:1:{i:0;s:5:"email";}s:4:"bank";a:0:{}}s:5:"phone";s:0:"";s:10:"show_email";s:2:"no";s:7:"address";a:6:{s:8:"street_1";s:0:"";s:8:"street_2";s:0:"";s:4:"city";s:0:"";s:3:"zip";s:0:"";s:7:"country";s:0:"";s:5:"state";s:0:"";}s:8:"location";s:0:"";s:6:"banner";i:0;s:4:"icon";i:0;s:8:"gravatar";i:0;s:14:"show_more_ptab";s:3:"yes";s:9:"store_ppp";i:12;s:10:"enable_tnc";s:3:"off";s:9:"store_tnc";s:0:"";s:23:"show_min_order_discount";s:2:"no";s:9:"store_seo";a:0:{}s:24:"dokan_store_time_enabled";s:2:"no";s:23:"dokan_store_open_notice";s:0:"";s:24:"dokan_store_close_notice";s:0:"";}

Also i have another table named user_stores ID | STORE Id is integer and store is string format.

I want to make trigger on update when table user_stores change some store name then change the a s:9:"STORENAME

s:9 is the length of the value, in our example is STORENAME

What have you tried? What is working wrong? what didn't work out? — Akina, Feb 14 '23 at 10:07
the format is bad, a proper json with unique tags, would be better or even a nirmalized table structure see https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — nbk, Feb 14 '23 at 10:34
@Akina that doesn't matter, xml json or serialized is all bad in my opinion, as long as you save and retrieve it ok, but when manipulation starts and you can't handle it on your own, iz will get wose — nbk, Feb 14 '23 at 10:38

Andriu1510 · Answer 1 · 2023-02-14T10:50:22.990

1

Assuming you're using MySQL, you can create a trigger on the user_stores table to update the data_table when a store name changes. Here's an example:

CREATE TRIGGER update_storename_trigger
AFTER UPDATE ON user_stores
FOR EACH ROW
BEGIN
  UPDATE data_table
  SET DATA = REPLACE(DATA, CONCAT('s:', LENGTH(OLD.STORE), ':"', OLD.STORE, '"'), CONCAT('s:', LENGTH(NEW.STORE), ':"', NEW.STORE, '"'))
  WHERE DATA LIKE CONCAT('%s:', LENGTH(OLD.STORE), ':"', OLD.STORE, '";%');
END;

This trigger will be executed after the user_stores table is updated and will update the DATA column of the data_table if the store name has changed. The REPLACE function is used to replace the old store name with the new store name in the serialized data stored in the DATA column. The WHERE clause is used to search the data table rows that contain the previous store name in the serialized data.

Note that this trigger assumes that the serialized data in the DATA column is in the serialized format. If the data is in a different format, the trigger will have to be modified.

edited Feb 14 '23 at 10:50

answered Feb 14 '23 at 10:21

Andriu1510

409
1
6

is there a good cause why you write in spanish? – nbk Feb 14 '23 at 10:31
that works only if the store_name in DATA string is the same with OLD.STORE. But this is not sure, at least the first time, or maybe data will be change by other query not from trigger, so the result is to have different STORE value in users_stores and ;s:9:"STORENAME" in data_table. Also another problem with REPLACE will find same string then replace everything – Achill Feb 14 '23 at 10:32
You cannot use `LIKE` operator, because `store` value may contain wildcard characters `%` or `_`. Use INSTR/LOCATE instead. – Akina Feb 14 '23 at 10:32
@Achill *that works only if the store_name in DATA string is the same with OLD.STORE* If this is not true then you cannot set the relation and determine does the row must be updated. – Akina Feb 14 '23 at 10:34
is any way to do with SUBSTRING_INDEX(DATA, 'store_name', 1) or something like that? to split the string at 3 1) a:19:{s:10:"store_name"; 2) s:9:"STORENAME" 3) s:6:"social";a:7:{s:2:"fb";s:0:"";s:7:"twitter";s:0:".....................................;} And then change only the 2) with new name and then CONCAT 1)2)3) – Achill Feb 14 '23 at 10:41

score 0 · Answer 2 · answered Feb 14 '23 at 12:00

The final answer who works is:

BEGIN
  UPDATE data_table as b
  SET b.DATA = CONCAT(SUBSTRING_INDEX(b.STORE, 'store_name', 1),'store_name\";s:',LENGTH(new.STORE),':\"',new.STORE,'\";s',SUBSTRING_INDEX(b.STORE, SUBSTRING_INDEX((SUBSTRING_INDEX(b.STORE, 'store_name', -1)), ':',3), -1))
  WHERE b.ID=new.ID
END;

Replace string in column value MYSQL

2 Answers2