How to set default value as empty string for all rows at once in MySQL while creating the table

Question

In MySQL, while creating a table, I'like to have empty string '' as value for all needed columns instead of 'NULL'.

Is it possible, is it safe and how can I do to do that at once?

I have the following version:

mysql  Ver 15.1 Distrib 10.1.45-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Answer 1

in first step if you want to create a table to prevent from NULL values and use '' as default you need to add the default attribute to that column as below:

CREATE TABLE users (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_name VARCHAR(30) NOT NULL DEFAULT ''
);

otherwise if you already have a table then need to take this two action:

1. You may update structure of table then run update query for data.
2. You have to update your data and then update the structure of table:

/* update table structure */
ALTER TABLE users MODIFY COLUMN user_name VARCHAR(255) NOT NULL DEFAULT '';

and then you can update the current NULL values by running update query:

/* update table data */
UPDATE users SET user_name = "" WHERE user_name IS NULL