How do I modify a MySQL column to allow NULL without specifying its data type?

Question

I have a table in mysql and I want to alter a table to allow a column to be null.

When I do describe on my mysql table, I see these things in mysql workbench:

Field       Type        Null        Key     Default     Extra

I want to set Null field as YES for a particular column. Here is how I am trying which works fine but do I need to provide its data type while setting DEFAULT to NULL?

ALTER TABLE abc.xyz CHANGE hello hello int(10) unsigned DEFAULT NULL;
ALTER TABLE abc.xyz CHANGE world world int(10) unsigned DEFAULT NULL;

Is there any other way by which I can just pick column name and set default to NULL instead of using its data type as well? I don't want to provide int(10) while setting its default to NULL.

It is best to think of NULL/NOT NULL as a part of the field's datatype (it does affect storage). If you are using Workbench, why not just use it's built in features for altering tables? — Uueerdo, Aug 13 '15 at 20:15
No. data type needs to be defined as part of table definition; or it has to inherit from an existing dataset or table. Is the problem you want to set NULL default values but the int value defined could vary from table to table field to field? (keep in mind NULL is the default value so why set it?) — xQbert, Aug 13 '15 at 20:15
[Possible duplicate](http://stackoverflow.com/questions/212939/how-do-i-modify-a-mysql-column-to-allow-null) — ODelibalta, Aug 13 '15 at 20:15

score 6 · Answer 1 · edited Aug 13 '15 at 21:22

6

You're kind of on the wrong track. Changing the default to NULL does not "allow" the column to be null: what you need to do is drop the "NOT NULL" constraint on the column.

But to redefine the nullability of a column through script, you will have to continue to reference the datatype. You'll have to enter something like

 ALTER TABLE MyTable MODIFY COLUMN this_column Int NULL;

edited Aug 13 '15 at 21:22

N.B.

13,688
3
45
55

answered Aug 13 '15 at 20:17

Curt

5,518
1
21
35

score 2 · Answer 2 · answered Aug 13 '15 at 20:36

Not sure if this applies if you're looking for a straight DDL statement, but in MySQL Workbench you can right-click on a table name, select "Alter Table..." and it will pull up a table definition GUI. From there, you can select null/not null (among all the other options) without explicitly listing the column's datatype. Just a "for what it's worth"...

score 1 · Answer 3 · answered Aug 16 '19 at 11:12

This can be done with the help of dynamic statements.

Usage:

CALL sp_make_nullable('schema_name', 'table_name', 'column_name', TRUE);

Implementation:

DELIMITER $$
create procedure eval(IN var_dynamic_statement text)
BEGIN
    SET @dynamic_statement := var_dynamic_statement;
    PREPARE prepared_statement FROM @dynamic_statement;
    EXECUTE prepared_statement;
    DEALLOCATE PREPARE prepared_statement;
END;

DELIMITER ;

DELIMITER $$
create procedure sp_make_nullable(IN var_schemaname varchar(64), IN var_tablename varchar(64),
                                  IN var_columnname VARCHAR(64), IN var_nullable BOOLEAN)
BEGIN
    DECLARE var_column_type LONGTEXT DEFAULT (SELECT COLUMN_TYPE
                                              FROM information_schema.COLUMNS
                                              WHERE TABLE_SCHEMA = var_schemaname
                                                AND TABLE_NAME = var_tablename
                                                AND COLUMN_NAME = var_columnname);

    DECLARE var_nullable_prefix VARCHAR(64) DEFAULT '';

    IF NOT var_nullable THEN
        SET var_nullable_prefix := 'NOT';
    end if;

    CALL eval(CONCAT('
        ALTER TABLE ', var_schemaname, '.', var_tablename,
        ' MODIFY ', var_columnname, ' ', var_nullable_prefix, ' NULL
    '));
end$$

DELIMITER ;

How do I modify a MySQL column to allow NULL without specifying its data type?

3 Answers3

Usage:

Implementation: