1

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
3
  • 3
    CREATE TABLE ... ( ..., column_name VARCHAR(...) DEFAULT '', ...
    – Akina
    Nov 12, 2020 at 7:45
  • 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.
    – John K. N.
    Nov 12, 2020 at 8:07
  • 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?
    – philipxy
    Nov 14, 2020 at 17:34

1 Answer 1

0

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

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.