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

• Akina: CREATE TABLE ... ( ..., column_name VARCHAR(...) DEFAULT '', ...
• John K. N.: Welcome to DBA.SE. This is a question that can be answered by consulting the MySQL manual for CREATE TABLE..... Basic questions about SQL syntax for the various flavours of DBMS out there should be asked over on Stack Overflow. See this quick search for results regarding your question. Have a look at What topics can I ask about here? for more information. Enjoy your journey.
• philipxy: Don't you mean, instead of NULL? Also what exactly does "have" "as value for all needed columns" mean? And what has your research about defaults shown that is relevant?

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

• msdos: UPDATE users SET user_name = "" please change double quotes to single