home

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

ค้นหาและแทนที่ข้อความในคอลัมน์ Text ด้วย SUBSTRING_INDEX และ CONCAT ใน MySQL

สถานการณ์

มีคอลัมน์เก็บรายการชื่อคั่นด้วย Comma ต้องการแทนที่ชื่อเฉพาะรายการ (เช่น 'John' ตัวที่ 2) เป็นชื่อใหม่ ('Adam') โดยไม่กระทบชื่อ 'John' ตัวแรก

วิธีแก้: ใช้ SUBSTRING_INDEX + CONCAT

  • SUBSTRING_INDEX(str, delim, count) - ตัดสตริงจาก Delimiter ครั้งที่ count (บวก=นับจากซ้าย, ลบ=นับจากขวา)
  • CONCAT(str1, str2, ...) - รวมสตริง

ตัวอย่างการใช้งาน

1. สร้างตาราง

mysql> CREATE TABLE DemoTable (
    -> ListOfName TEXT
    -> );
Query OK, 0 rows affected (0.66 sec)

2. เพิ่มข้อมูล

mysql> INSERT INTO DemoTable VALUES('Carol,Sam,John,David,Bob,Mike,Robert,John,Chris,James,Jace');
Query OK, 1 row affected (0.13 sec)

3. แสดงข้อมูลก่อน

mysql> SELECT * FROM DemoTable;

ผลลัพธ์:

+------------------------------------------------------------+
| ListOfName                                                 |
+------------------------------------------------------------+
| Carol,Sam,John,David,Bob,Mike,Robert,John,Chris,James,Jace |
+------------------------------------------------------------+
1 row in set (0.00 sec)

4. แทนที่ 'John' ตัวที่ 2 (นับจากซ้าย) เป็น 'Adam'

mysql> UPDATE DemoTable
    -> SET ListOfName = 
    -> CONCAT(
    ->     SUBSTRING_INDEX(ListOfName, 'John', 2),  -- ส่วนก่อน 'John' ตัวที่ 2
    ->     'Adam',                                   -- ค่าใหม่
    ->     SUBSTRING_INDEX(ListOfName, 'John', -1)   -- ส่วนหลัง 'John' ตัวสุดท้าย
    -> );

ผลลัพธ์: Query OK, 1 row affected (0.37 sec)

5. ตรวจสอบผลลัพธ์

mysql> SELECT * FROM DemoTable;

ผลลัพธ์:

+------------------------------------------------------------+
| ListOfName                                                 |
+------------------------------------------------------------+
| Carol,Sam,John,David,Bob,Mike,Robert,Adam,Chris,James,Jace |
+------------------------------------------------------------+
1 row in set (0.00 sec)

อธิบาย Logic

  1. SUBSTRING_INDEX(ListOfName, 'John', 2) → หา 'John' ตัวที่ 2 จากซ้าย คืนค่าส่วนก่อนหน้า: 'Carol,Sam,John,David,Bob,Mike,Robert,'
  2. SUBSTRING_INDEX(ListOfName, 'John', -1) → หา 'John' ตัวสุดท้ายจากขวา คืนค่าส่วนหลัง: ',Chris,James,Jace'
  3. CONCAT(ส่วนก่อน, 'Adam', ส่วนหลัง) → รวมกลับกัน

ข้อจำกัดและข้อควรระวัง

  • ทำงานได้เฉพาะกรณีที่รู้ลำดับของค่าที่ต้องการแทนที่ (ตัวที่ N)
  • หากค่าที่ค้นหาซ้ำกันและต้องการแทนที่ทุกตัว → ใช้ REPLACE() แทน
  • Performance ไม่ดีบนข้อมูลเยอะ (Full scan + String manipulation)
  • ข้อมูลแบบ Comma-separated ใน 1 คอลัมน์違反 1NF ควร Normalize เป็นตารางความสัมพันธ์

แนวทางที่ดีกว่า (Normalized Design)

-- ตารางหลัก
CREATE TABLE NameLists (Id INT PRIMARY KEY, ListName VARCHAR(50));

-- ตารางรายการ (Normalized)
CREATE TABLE NameListItems (
    ListId INT,
    ItemOrder INT,
    Name VARCHAR(20),
    PRIMARY KEY (ListId, ItemOrder),
    FOREIGN KEY (ListId) REFERENCES NameLists(Id)
);

-- แทนที่ง่ายด้วย UPDATE ธรรมดา
UPDATE NameListItems SET Name = 'Adam' WHERE ListId = 1 AND ItemOrder = 8;