How do I automatically store the timestamp of a record\'s creation in MySQL?

Automatic Record Creation Timestamping in MySQL

In MySQL, you may encounter a situation where you want to automatically store the timestamp of a record's creation upon insertion. To achieve this, you cannot rely on the timestamp data type with current_timestamp as the default value, as it will be updated every time the record is modified. Instead, there's a more specific solution available.

To automatically store the creation timestamp, you can use the DEFAULT constraint with CURRENT_TIMESTAMP. Here's how:

For a new table:

<code class="sql">CREATE TABLE your_table (
  ...
  your_date_column DATETIME DEFAULT CURRENT_TIMESTAMP
  ...
);</code>

For an existing table:

<code class="sql">ALTER TABLE your_table
ALTER COLUMN date_column SET DEFAULT CURRENT_TIMESTAMP;</code>

By applying this constraint, whenever a new record is inserted without explicitly specifying a value for the date_column, the current date and time at the moment of insertion will be automatically used. NULL and DEFAULT are both valid values to trigger the default constraint, assuming the column is nullable.

The above is the detailed content of How do I automatically store the timestamp of a record\'s creation in MySQL?. For more information, please follow other related articles on the PHP Chinese website!