Fungsi MySQL CAST() memungkinkan Anda mengonversi nilai ekspresi dari satu jenis ke jenis lainnya
Misalnya, Anda dapat mengubah nilai jenis CHAR_ menjadi nilai +--------------------------------+ | CAST('2020-10-02' AS DATETIME) | +--------------------------------+ | 2020-10-02 00:00:00 | +--------------------------------+ 0 sebagai berikut
SELECT CAST('2020-10-02' AS DATETIME); _
Kueri SQL di atas akan menghasilkan rangkaian hasil berikut
+--------------------------------+ | CAST('2020-10-02' AS DATETIME) | +--------------------------------+ | 2020-10-02 00:00:00 | +--------------------------------+
Sintaks untuk fungsi CAST() adalah +--------------------------------+ | CAST('2020-10-02' AS DATETIME) | +--------------------------------+ | 2020-10-02 00:00:00 | +--------------------------------+ 2
Anda dapat menempatkan nilai literal atau nama kolom sebagai argumen untuk parameter +--------------------------------+ | CAST('2020-10-02' AS DATETIME) | +--------------------------------+ | 2020-10-02 00:00:00 | +--------------------------------+ 3
Parameter +--------------------------------+ | CAST('2020-10-02' AS DATETIME) | +--------------------------------+ | 2020-10-02 00:00:00 | +--------------------------------+ 4 harus dari tipe data MySQL yang valid. Itu bisa menjadi salah satu dari yang berikut
- CHAR mengubah ekspresi menjadi tipe +--------------------------------+ | CAST('2020-10-02' AS DATETIME) | +--------------------------------+ | 2020-10-02 00:00:00 | +--------------------------------+ 6
- +--------------------------------+ | CAST('2020-10-02' AS DATETIME) | +--------------------------------+ | 2020-10-02 00:00:00 | +--------------------------------+ 7 mengubah ekspresi menjadi tipe +--------------------------------+ | CAST('2020-10-02' AS DATETIME) | +--------------------------------+ | 2020-10-02 00:00:00 | +--------------------------------+ 8
- +--------------------------------+ | CAST('2020-10-02' AS DATETIME) | +--------------------------------+ | 2020-10-02 00:00:00 | +--------------------------------+ 0 mengubah ekspresi menjadi tipe +--------------------------------+ | CAST('2020-10-02' AS DATETIME) | +--------------------------------+ | 2020-10-02 00:00:00 | +--------------------------------+ 0 dengan format +----+------------+------------+ | id | first_name | join_date | +----+------------+------------+ | 1 | Jackson | 2021-09-22 | | 2 | Sarah | 2021-09-23 | | 3 | Akihiko | 2021-09-24 | | 4 | Matt | 2021-09-25 | +----+------------+------------+ 1
- +----+------------+------------+ | id | first_name | join_date | +----+------------+------------+ | 1 | Jackson | 2021-09-22 | | 2 | Sarah | 2021-09-23 | | 3 | Akihiko | 2021-09-24 | | 4 | Matt | 2021-09-25 | +----+------------+------------+ _2 mengubah ekspresi menjadi tipe +----+------------+------------+ | id | first_name | join_date | +----+------------+------------+ | 1 | Jackson | 2021-09-22 | | 2 | Sarah | 2021-09-23 | | 3 | Akihiko | 2021-09-24 | | 4 | Matt | 2021-09-25 | +----+------------+------------+ 2 dengan format +----+------------+------------+ | id | first_name | join_date | +----+------------+------------+ | 1 | Jackson | 2021-09-22 | | 2 | Sarah | 2021-09-23 | | 3 | Akihiko | 2021-09-24 | | 4 | Matt | 2021-09-25 | +----+------------+------------+ 4
- +----+------------+------------+ | id | first_name | join_date | +----+------------+------------+ | 1 | Jackson | 2021-09-22 | | 2 | Sarah | 2021-09-23 | | 3 | Akihiko | 2021-09-24 | | 4 | Matt | 2021-09-25 | +----+------------+------------+ _5 mengubah ekspresi menjadi tipe +----+------------+------------+ | id | first_name | join_date | +----+------------+------------+ | 1 | Jackson | 2021-09-22 | | 2 | Sarah | 2021-09-23 | | 3 | Akihiko | 2021-09-24 | | 4 | Matt | 2021-09-25 | +----+------------+------------+ 5 dengan format +----+------------+------------+ | id | first_name | join_date | +----+------------+------------+ | 1 | Jackson | 2021-09-22 | | 2 | Sarah | 2021-09-23 | | 3 | Akihiko | 2021-09-24 | | 4 | Matt | 2021-09-25 | +----+------------+------------+ 7
- +----+------------+------------+ | id | first_name | join_date | +----+------------+------------+ | 1 | Jackson | 2021-09-22 | | 2 | Sarah | 2021-09-23 | | 3 | Akihiko | 2021-09-24 | | 4 | Matt | 2021-09-25 | +----+------------+------------+ _8 mengubah ekspresi menjadi tipe +----+------------+------------+ | id | first_name | join_date | +----+------------+------------+ | 1 | Jackson | 2021-09-22 | | 2 | Sarah | 2021-09-23 | | 3 | Akihiko | 2021-09-24 | | 4 | Matt | 2021-09-25 | +----+------------+------------+ 8. Kembalikan nilai tahun antara SELECT id, first_name, join_date FROM members WHERE join_date BETWEEN CAST('2021-09-23' AS DATE) AND CAST('2021-09-24' AS DATE); _0 inklusif
- SELECT id, first_name, join_date FROM members WHERE join_date BETWEEN CAST('2021-09-23' AS DATE) AND CAST('2021-09-24' AS DATE); 1 mengubah ekspresi menjadi SELECT id, first_name, join_date FROM members WHERE join_date BETWEEN CAST('2021-09-23' AS DATE) AND CAST('2021-09-24' AS DATE); 1 bilangan bulat positif-negatif
- SELECT id, first_name, join_date FROM members WHERE join_date BETWEEN CAST('2021-09-23' AS DATE) AND CAST('2021-09-24' AS DATE); 3 mengubah ekspresi menjadi SELECT id, first_name, join_date FROM members WHERE join_date BETWEEN CAST('2021-09-23' AS DATE) AND CAST('2021-09-24' AS DATE); 3 bilangan bulat positif dari SELECT id, first_name, join_date FROM members WHERE join_date BETWEEN CAST('2021-09-23' AS DATE) AND CAST('2021-09-24' AS DATE); 5 menjadi SELECT id, first_name, join_date FROM members WHERE join_date BETWEEN CAST('2021-09-23' AS DATE) AND CAST('2021-09-24' AS DATE); 6
- SELECT id, first_name, join_date FROM members WHERE join_date BETWEEN CAST('2021-09-23' AS DATE) AND CAST('2021-09-24' AS DATE); 7, SELECT id, first_name, join_date FROM members WHERE join_date BETWEEN CAST('2021-09-23' AS DATE) AND CAST('2021-09-24' AS DATE); 8, SELECT id, first_name, join_date FROM members WHERE join_date BETWEEN CAST('2021-09-23' AS DATE) AND CAST('2021-09-24' AS DATE); 9 mengonversi ekspresi menjadi angka mengambang dengan berbagai presisi
- +----+------------+------------+ | id | first_name | join_date | +----+------------+------------+ | 2 | Sarah | 2021-09-23 | | 3 | Akihiko | 2021-09-24 | +----+------------+------------+ 0 mengubah ekspresi menjadi tipe +----+------------+------------+ | id | first_name | join_date | +----+------------+------------+ | 2 | Sarah | 2021-09-23 | | 3 | Akihiko | 2021-09-24 | +----+------------+------------+ 0
Fungsi CAST()_ sangat berguna untuk memfilter kueri Anda dan menghasilkan rangkaian hasil yang lebih spesifik
Misalnya, bayangkan Anda memiliki tabel ________30______3 berikut di database MySQL Anda
+----+------------+------------+ | id | first_name | join_date | +----+------------+------------+ | 1 | Jackson | 2021-09-22 | | 2 | Sarah | 2021-09-23 | | 3 | Akihiko | 2021-09-24 | | 4 | Matt | 2021-09-25 | +----+------------+------------+
Misalkan Anda ingin +----+------------+------------+ | id | first_name | join_date | +----+------------+------------+ | 2 | Sarah | 2021-09-23 | | 3 | Akihiko | 2021-09-24 | +----+------------+------------+ _4 baris tertentu yang memiliki nilai +----+------------+------------+ | id | first_name | join_date | +----+------------+------------+ | 2 | Sarah | 2021-09-23 | | 3 | Akihiko | 2021-09-24 | +----+------------+------------+ 5 antara tanggal 23 dan 24 September
Anda dapat menggunakan fungsi CAST()_ di klausa +----+------------+------------+ | id | first_name | join_date | +----+------------+------------+ | 2 | Sarah | 2021-09-23 | | 3 | Akihiko | 2021-09-24 | +----+------------+------------+ 7 sebagai berikut
SELECT id, first_name, join_date FROM members WHERE join_date BETWEEN CAST('2021-09-23' AS DATE) AND CAST('2021-09-24' AS DATE);
Permintaan di atas akan menghasilkan hasil berikut
+----+------------+------------+ | id | first_name | join_date | +----+------------+------------+ | 2 | Sarah | 2021-09-23 | | 3 | Akihiko | 2021-09-24 | +----+------------+------------+
Casting dari VARCHAR ke INT
Seperti yang Anda lihat pada daftar di atas, tipe +----+------------+------------+ | id | first_name | join_date | +----+------------+------------+ | 2 | Sarah | 2021-09-23 | | 3 | Akihiko | 2021-09-24 | +----+------------+------------+ 8 tidak dianggap sebagai tipe yang valid di MySQL, jadi Anda tidak dapat mengonversi tipe +--------------------------------+ | CAST('2020-10-02' AS DATETIME) | +--------------------------------+ | 2020-10-02 00:00:00 | +--------------------------------+ 6 menjadi tipe +----+------------+------------+ | id | first_name | join_date | +----+------------+------------+ | 2 | Sarah | 2021-09-23 | | 3 | Akihiko | 2021-09-24 | +----+------------+------------+ 8 menggunakan kueri berikut
SELECT CAST('200' AS INT); -- Error SELECT CAST('200' AS INTEGER); -- Error
Kedua kueri di atas akan menyebabkan MySQL melempar kesalahan
Untuk mengonversi ekspresi Anda menjadi tipe +----+------------+------------+ | id | first_name | join_date | +----+------------+------------+ | 2 | Sarah | 2021-09-23 | | 3 | Akihiko | 2021-09-24 | +----+------------+------------+ 8, Anda harus menggunakan tipe integer SELECT id, first_name, join_date FROM members WHERE join_date BETWEEN CAST('2021-09-23' AS DATE) AND CAST('2021-09-24' AS DATE); 1 atau SELECT id, first_name, join_date FROM members WHERE join_date BETWEEN CAST('2021-09-23' AS DATE) AND CAST('2021-09-24' AS DATE); 3 seperti yang ditunjukkan di bawah ini