mysql - Query to replace specific character in a table -

i have table contain many fields. want want make single query update every field in table contain \' \\\'. 1 field query

update table set column1 = replace(column1, '\'', '\\\'');

but if have replace same thing in fields. have write same query every field ? there way write 1 query update fields.

q: have write same query every field? there way write 1 query update fields?

each column want update need referenced. can update multiple columns in single statement. (you don't have run separate statements; more efficient update them in 1 fell swoop.

 update mytable      set col1 = expr1       , col2 = expr2        , col3 = expr3 

but need explicitly reference each column want update.

---

we can use sql statement column names. likely, we'll want exclude integer/decimal/float columns, enum, set, date, datetime, timestamp, etc. here's example of query gets column names:

  select c.column_name     information_schema.columns    c.table_schema = 'mydatabase'      , c.table_name   = 'mytable' 

note: backslash character escape character in mysql literals,

to represent backslash , single quote string literal

  '\\''' 

to represent 3 backslashes , single quote string literal

  '\\\\\\''' 

if wanted replace occurrences of single quote, literal backslash , single quote, i'd use expression this:

  replace(foo, '''', '\\''')  

when need this, reference bunch of columns, i'll use query generate expressions; this:

  select concat(',`',c.column_name,'` = replace(`',c.column_name             ,'`,'''''''',''\\\\'''''')') expr     information_schema.columns c    c.table_schema = 'mydatabase'      , c.table_name   = 'mytable'      , c.data_type in ('char','varchar') 

then can take values returned expr , use in subsequent statement. aware of increasing number of characters in column, doesn't exceed maximum size.