Setting Dynamic Defaults in MySQL: Exploring Triggers and Alternatives

• MySQL expects a constant value: The DEFAULT clause in MySQL requires a fixed and unchanging value, such as a literal number, string, or NULL. Functions, on the other hand, can return different values depending on the context, making them unsuitable for defaults.
• Ensuring data integrity: Using functions in defaults could lead to unexpected behavior and data inconsistencies. For example, a function that generates unique IDs might return the same value for multiple rows if called during insertion.

However, there are alternative solutions:

Example using a trigger:

CREATE TABLE `users` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `created_at` DATETIME NOT NULL DEFAULT NULL
);

CREATE TRIGGER `set_created_at` BEFORE INSERT ON `users`
FOR EACH ROW
SET NEW.created_at = NOW();

This example creates a table with a created_at column and a trigger named set_created_at. The trigger automatically sets the created_at value to the current timestamp (NOW()) whenever a new row is inserted.