MySQL字段值在SQL查询中的时间

Question

In a MySQL database I have a column called 'mem_expire' which contains date values in the format of '24/01/2010'.

I am trying to use some conditional statements to delete all records where the expiry date is less than a pre-set time variable. Here is my code but i'm not sure if i am going about it the right way OR whether you can use PHP functions in a SQL query?

Code: / Delete Expired Members

    $oneyear = time() - (52 * 7 * 24 * 60 * 60);
    $fiveyears = time() - (5 * 52 * 7 * 24 * 60 * 60);
    $tenyears = time() - (10 * 52 * 7 * 24 * 60 * 60);

    if (isset($_POST['delete'])) {
        $delete = mysqli_real_escape_string($connection, $_POST['deleteold']);

        if ($delete == '1') {
            $deletequery = "DELETE FROM members WHERE" . strtotime('mem_expire') ." < $oneyear";
        } elseif ($delete == '5') {
            $deletequery = "DELETE FROM members WHERE" . strtotime('mem_expire') . "< $fiveyears";
        } elseif ($delete == '10') {
            $deletequery = "DELETE FROM members WHERE" . strtotime('mem_expire') . "< $tenyears";
        }

        $deleteresult = mysqli_query($connection, $deletequery);
    }

You can see what i am trying to do but i'm not going about it the right way, can someone point me in the right direction please. Any help is good help :)

Answer 1

You may make use of STR_TO_DATE (see also http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_str-to-date) in the WHERE clause of your DELETE statement. So this could read something like this:

DELETE FROM members WHERE STR_TO_DATE(mem_expire, '%d/%m/%y') < somevalue

Moreover, there is the INTERVAL syntax in MySQL, which may come in handy here. It allows you to specify a date range which shall be subtracted from another relative value. Thus, you may be able to make the command even more readable by using:

DELETE FROM members WHERE STR_TO_DATE(mem_expire, '%d/%m/%y') < DATE_SUB(now(), INTERVAL 1 YEAR);

In such date cases, http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html is always worth a look...

NB: This call to STR_TO_DATE() might become quite costly, if you have many records in your member table. Background to this is the fact that a full table scan needs to be performed by the DBMS system, as no index can be used for this. If your operation is getting time critical, you really should think about what @DanielHarris commented: change the datatype to DATE if possible. Alternatively, if that would have major impact to the entire rest of your coding, it might be an option to use generated columns (available only with MySQL 5.7 or above).