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.

Improve this question

1 Answer 1

Reset to default
3

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

Improve this answer
3
  • 1
    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
    Commented Jul 22, 2016 at 16:45
  • If a text column is empty it will just be an empty string
    – Florian Humblot
    Commented Jul 22, 2016 at 17:23
  • 1
    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
    – Florian Humblot
    Commented Jul 22, 2016 at 19:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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