How do I alter a mysql table column defaults?

by

Pete was almost correct but used the wrong syntax for ‘change’:

ALTER TABLE mytable CHANGE `time` `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

Notice that you must repeat the column name. Also, make sure you are using backticks instead of single quotes to escape the column name time, which prevents it from being interpreted as the mysql column type of time.

By specifying the DEFAULT of CURRENT_TIMESTAMP, MySQL will no longer automatically update the column. From the MySQL Manual:

With a DEFAULT CURRENT_TIMESTAMP clause and no ON UPDATE clause, the column has the current timestamp for its default value but is not automatically updated.

More Related Contents:

  1. ‘IF’ in ‘SELECT’ statement – choose output value based on column values
  2. 1052: Column ‘id’ in field list is ambiguous
  3. How to do a batch insert in MySQL
  4. Count the occurrences of DISTINCT values
  5. Codeigniter – multiple database connections
  6. JOIN queries vs multiple queries
  7. Is the primary key automatically indexed in MySQL?
  8. How do I modify a MySQL column to allow NULL?
  9. Storing Data in MySQL as JSON
  10. MySql: MyISAM vs. Inno DB! [closed]
  11. MySQL: Cloning a MySQL database on the same MySql instance
  12. DOs and DONTs for Indexes [closed]
  13. How to change the default port of mysql from 3306 to 3360
  14. How to move columns in a MySQL table?
  15. How to insert images from database?
  16. Is there an SQLite equivalent to MySQL’s DESCRIBE [table]?
  17. When to use MyISAM and InnoDB? [duplicate]
  18. Alternative to using LIMIT keyword in a SubQuery in MYSQL
  19. How to insert a file in MySQL database?
  20. How do I rename a MySQL database (change schema name)?
  21. Foreign key to one of many tables?
  22. Find date range overlaps within the same table, for specific user MySQL
  23. Rename a table in MySQL
  24. What’s the recommended way to connect to MySQL from Go? [closed]
  25. How to store Query Result in variable using mysql
  26. How to find all the relations between all mysql tables?
  27. How to speed up sql queries ? Indexes?
  28. How to reuse auto_increment values? [duplicate]
  29. Are there any functions in MySQL like dense_rank() and row_number() like Oracle?
  30. Optimistic locking in MySQL

Leave a Comment