1

Mysql does not support default values for TEXT columns. I have a live database with NULL values

What is the proper way to change all null values to a predefined value and change the column to not null? I would like to do this in a single transaction to prevent new NULL values being inserted between updating all old NULL values and ALTERING the column.

1

Run this query: UPDATE tablename SET textname = "" WHERE textname IS NULL after running that you can change the table structure so it does not allow null values anymore.

I recommend checking the code to make sure it doesn't check for null instead of empty strings before changing the way the dB behaves

  • Thanks FMashiro! The problem though is that this is a live system where new rows are constantly being inserted, some of which might have NULL values. So, I can't guarantee that there won't be new null values inserted between running the UPDATE and the ALTER statements. I would normally handle this by setting a default value for the column, but as mysql doesn't support default values for TEXT, I'm at a loss – sorenbs Jul 22 '16 at 16:45
  • If a text column is empty it will just be an empty string – FMashiro Jul 22 '16 at 17:23
  • Oh and also, before doing the database changes, make the changes to the code where needed, so that it does not allow null values anymore – FMashiro Jul 22 '16 at 19:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.