Mysql add default value for all date field

Question

I must to change default value for all date field in my database. Do you know how to do that fast.

Thanks

update the table definitions in the [INFORMATION_SCHEMA](http://dev.mysql.com/doc/refman/5.0/en/information-schema.html) db. — Marc B, Feb 27 '13 at 22:03
Do you want to update the existing values or do you need to set a default value when a row is inserted ? — jazzytomato, Feb 27 '13 at 22:04
@Marc B: information_schema tables are read-only (actually, they're not even tables but views) — Mchl, Feb 27 '13 at 22:06
ah yeah... true enough. but at least you can query it to find all the date/datetime fields in a db, and then alter those separately. — Marc B, Feb 27 '13 at 22:10

score 0 · Answer 1 · answered Feb 27 '13 at 22:26

Try something like that :

SELECT
    CONCAT('ALTER TABLE `', TABLES.TABLE_NAME, '` ALTER COLUMN `', COLUMNS.COLUMN_NAME, '` SET DEFAULT ''2013-02-27'';')

    INTO OUTFILE 'C:\\queries.sql'

    FROM TABLES
        INNER JOIN COLUMNS
            ON COLUMNS.TABLE_SCHEMA = TABLES.TABLE_SCHEMA
            AND COLUMNS.TABLE_NAME = TABLES.TABLE_NAME

    WHERE
        TABLES.TABLE_TYPE = 'BASE TABLE' 
        AND TABLES.table_schema = 'your_database'
        AND data_type = 'date';

You'll have to adapt

the outfile's path (here C:/queries.sql on a Windows server...)
your database name
and obviously the default value you'll set (here 2013-02-27).

Then, you'll have to run the output script on the target database.

Mysql add default value for all date field

1 Answers1