Alter TEXT column to NOT NULL when there is existing NULL rows

Question

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.

Answer 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

• sorenbs: 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
• Florian Humblot: If a text column is empty it will just be an empty string
• Florian Humblot: 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