how to insert date in mysql table
I have a mysql table called pollOfTheWeek. It has a column "pollDate" of type date. I have two questions reg开发者_运维问答arding this : 1. I declared the column while creating the table as [pollDate date] What I wanted is that this column be set automatically, when the user doesnt enter any value for this column. How do i declare the column to achieve this?
- Assuming that I have the same declaration as above, how do I enter an empty value. I mean if the column was of type varchar, I would enter empty value as " ". But since it is of type date, I get error when I enter the value as " ". How do I enter empty value for the pollDate column?
You need to create a trigger:
CREATE TRIGGER triggerName
BEFORE INSERT ON yourTable
FOR EACH ROW
SET NEW.date = coalesce(NEW.date, curdate());
The empty value would be null
.
MySQL has an extremely limited support for default values. In general, it must be a constant expression like '0'
or '(none)'
and functions are not allowed. The only exception I'm aware of is that you can set CURRENT_TIMESTAMP
as default value for a TIMESTAMP
column; however, this is not available for DATE
or DATETIME
columns. Unluckily, TIMESTAMP
has a smaller range and contains time information.
So your options are:
- Do it in your client application by setting NOW() as value in your inserts.
- Write a trigger:
.
CREATE TRIGGER pollOfTheWeek_trg BEFORE INSERT ON pollOfTheWeek
FOR EACH ROW SET NEW.pollDate = NOW();
As about empty values, I would simply use NULL. Why use empty strings?
there is no way to automatically set a DATE
column to the current date/time in mysql. TIMESTAMP
s can be automatically set to the current time though. alternatively, just pass NOW()
as a parameter when you insert :
INSERT INTO pollOfTheWeek (name, pollDate) VALUES ('someName', NOW());
as to your other question, you probably want to allow NULL values in the date column and set them to NULL when there is no date given.
精彩评论