home

หน้าแรก
 Computer >> บทช่วยสอนคอมพิวเตอร์ >  >> การเขียนโปรแกรม >> MySQL

MySQL: เปรียบเทียบวันที่ใน VARCHAR กับวันที่ปัจจุบันด้วย STR_TO_DATE

การแปลง VARCHAR เป็น DATE เพื่อเปรียบเทียบกับ CURDATE()

เมื่อวันที่เก็บเป็น VARCHAR (เช่น '01/04/2019') ต้องแปลงเป็น DATE ด้วย STR_TO_DATE() ก่อนจึงจะเปรียบเทียบกับ CURDATE() ได้ถูกต้อง

สร้างตารางและข้อมูล

mysql> create table DemoTable1397 (
    -> AdmissionDate varchar(40)
    -> );
Query OK, 0 rows affected (0.97 sec)

mysql> insert into DemoTable1397 values('01/04/2019');
mysql> insert into DemoTable1397 values('27/09/2019');
mysql> insert into DemoTable1397 values('29/09/2018');
mysql> insert into DemoTable1397 values('29/09/2019');

ข้อมูลและวันที่ปัจจุบัน

mysql> select * from DemoTable1397;
+---------------+
| AdmissionDate |
+---------------+
| 01/04/2019    |
| 27/09/2019    |
| 29/09/2018    |
| 29/09/2019    |
+---------------+

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2019-09-27 |
+------------+

คำสั่งเปรียบเทียบ (วันที่มากกว่าวันนี้)

mysql> select * from DemoTable1397
    -> where str_to_date(AdmissionDate, '%d/%m/%Y') > curdate();

ผลลัพธ์

+---------------+
| AdmissionDate |
+---------------+
| 29/09/2019    |
+---------------+

Format Specifiers สำหรับ STR_TO_DATE

Specifierความหมายตัวอย่าง
%dวัน (01-31)01, 27, 29
%mเดือน (01-12)04, 09
%Yปี 4 หลัก2019
%yปี 2 หลัก19
%Hชั่วโมง 24h (00-23)14
%iนาที (00-59)30
%sวินาที (00-59)45

รูปแบบวันที่พบบ่อย

  • '%d/%m/%Y' — 29/09/2019 (ไทย, ยุโรป)
  • '%m/%d/%Y' — 09/29/2019 (US)
  • '%Y-%m-%d' — 2019-09-29 (ISO, MySQL native)
  • '%d-%b-%Y' — 29-Sep-2019

ประสิทธิภาพและ Index

STR_TO_DATE() ใน WHERE clause ทำให้ index บนคอลัมน์ VARCHAR ไม่ได้ใช้งาน (non-sargable)

แนวทางแก้:

  1. เปลี่ยนคอลัมน์เป็น DATE หรือ DATETIME type (Best)
  2. เพิ่ม Generated Column (MySQL 5.7+): AdmissionDateParsed DATE GENERATED ALWAYS AS (STR_TO_DATE(AdmissionDate, '%d/%m/%Y')) STORED แล้ว index คอลัมน์นี้
  3. แปลงข้อมูลครั้งเดียว (ETL) แล้วเก็บเป็น DATE