仅在每个月的第一天插入数据一次

I want to insert data into a table on 1st day of every month. And it has to be just for one time. I mean one row for each 1st day of month. I am doing all this in a PHP file and using mySql.

So far i got this much-

if(Date('j')==1)
{
    $query = select 1 from table where extract (year from t1) = extract(year from now()) and extract(month from t1) = extract(month from now()) LIMIT 1
    if (@mysql_num_rows(mysql_query($query)) == 0)
    {
       //perform insert operation
    }
}

I haven't tried this query till yet.

SELECT DATEADD(mm, DATEDIFF(mm,0,GETDATE())-1,0) as StartOfLastMonth
SELECT DATEADD(mm, DATEDIFF(mm,0,GETDATE()),0) as StartOfThisMonth
SELECT DATEADD(mm, DATEDIFF(mm,0,GETDATE()),0) as StartOfNextMonth

Try out below event scheduler

CREATE EVENT myevent
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MONTH
    DO
      "YOUR QUERY"

Your have to run this on the date on which you want to run on 1 month interval.

You get more information on http://dev.mysql.com/doc/refman/5.1/ja/create-event.html

you can achieve with mysql event scheduler--

http://dev.mysql.com/doc/refman/5.1/en/events.html

CREATE EVENT IF NOT EXISTS `my_old_data_cleaner_event`
ON SCHEDULE EVERY 23 DAY_HOUR
COMMENT 'Clean up Service Start at 11:00PM daily!'
DO DELETE FROM my_table WHERE created_date < (NOW() - INTERVAL 1 MONTH);

OR for Stored Procedure.

CREATE EVENT IF NOT EXISTS `my_old_data_cleaner_event`
ON SCHEDULE EVERY 23 DAY_HOUR
DO CALL my_sp_cleanup_old_data();

You have two solutions, depending on what you exactly want.

If your data must be inserted at the first day: the only way is to insert it using a cron job which run at the correct time. You can use the mysql scheduler to do the job if it's only mysql related.

If your data need to be in the table after the first day, but you don't care if it is added in realtime or only when you access that data then you don't need a cron job. You can check for that data when you access it, then insert it before reading if it's not already present.

Maybe this will do the trick

SELECT * FROM xxx WHERE (DATE_FORMAT(CURDATE() ,'%d')==1)