我在MySQL中的数据有问题。我有一列"vacation_period",我在其中保存用逗号分隔的日期的数据,例如。"02/10/2015,02/11/2015",我想炸逗号,月份和年份。我想制作条件,我可以将 POST 发送的月份和年份与 MySQL 字段中的分解数据进行比较。
示例查询:
SELECT *
FROM ag_vacations INNER JOIN
ag_employees
ON ag_vacations.user_id = ag_employees.e_id
where ag_vacations.user_id = 1 AND
ag_vacations.vacation_period = 2015 AND
ag_vacations.vacation_period = 02
order by date_added desc
所以试试这个方式:
SELECT *
FROM ag_vacations INNER JOIN
ag_employees
ON ag_vacations.user_id = ag_employees.e_id
where ag_vacations.user_id = 1 AND
((ag_vacations.vacation_period REGEXP '[0-9]{2}'/[0-9]{2}'/2015',[0-9]{2}'/[0-9]{2}'/[0-9]{4}' AND
ag_vacations.vacation_period REGEXP '02'/[0-9]{2}'/[0-9]{4}',[0-9]{2}'/[0-9]{2}'/[0-9]{4}')
OR (ag_vacations.vacation_period REGEXP '[0-9]{2}'/[0-9]{2}'/[0-9]{4}',[0-9]{2}'/[0-9]{2}'/2015' AND
ag_vacations.vacation_period REGEXP '[0-9]{2}'/[0-9]{2}'/[0-9]{4}',02'/[0-9]{2}'/[0-9]{4}'))
order by date_added desc
因此,如果您只需要检查该字符串02/10/2015,02/11/2015
中的第一个日期 - 您可以删除 OR 部分:
OR (ag_vacations.vacation_period REGEXP '[0-9]{2}'/[0-9]{2}'/[0-9]{4}',[0-9]{2}'/[0-9]{2}'/2015' AND
ag_vacations.vacation_period REGEXP '[0-9]{2}'/[0-9]{2}'/[0-9]{4}',02'/[0-9]{2}'/[0-9]{4}')
虽然一串日期并不理想,但我们有时会以这种方式接收数据。如果需要在有机会清理架构之前查询此内容,并且日期格式一致,则可以使用 like
查询字符串。在此查询中,我们将在ag_vacations.vacation_period
列的开头和结尾concat
逗号,以确保我们得到月份日期的开头和年份日期的结束:
SELECT *
FROM ag_vacations INNER JOIN
ag_employees
ON ag_vacations.user_id = ag_employees.e_id
where ag_vacations.user_id = 1 AND
concat(ag_vacations.vacation_period,',') like '%/2015,%' AND
concat(',',ag_vacations.vacation_period like '%,02/%'
order by date_added desc
如果你只想找到行,你不需要在 php 中爆炸。您可以使用FIND_IN_SET
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set