能否在CASE语句的IN条件中使用LIKE或CONTAINS？游戏库批量更新需求

你可以通过将family字段转换为单数形式，再复用原有的IN匹配列表来批量处理复数情况，不用逐个添加复数条目。以下是具体实现：

方法1：字符串处理转单数后匹配（通用兼容多数数据库） #

利用字符串函数去掉复数结尾的s或es，把复数转换成单数后再匹配原有的IN列表。以MySQL为例：

UPDATE monster
SET ecosystem = (
    CASE
        WHEN TRIM(TRAILING 'es' FROM TRIM(TRAILING 's' FROM family)) IN (
            'Behemoth', 'Buffalo', 'Cehuetzi', 'Cerberus', 'Coeurl', 
            'Dhalmel', 'Gnole', 'Manticore', 'Marid', 'Opo-opo', 
            'Raaz', 'Rabbit', 'Ram', 'Sheep', 'Tiger', 'Yztarg'
        ) THEN 'Beast'
        WHEN TRIM(TRAILING 'es' FROM TRIM(TRAILING 's' FROM family)) IN (
            'Adamantoise', 'Bugard', 'Eft', 'Gabbrath', 'Hill', 
            'Lizard', 'Matamata', 'Peiste', 'Raptor', 'Wivre'
        ) THEN 'Lizard'
        WHEN TRIM(TRAILING 'es' FROM TRIM(TRAILING 's' FROM family)) IN (
            'Antlion','Bee','Beetle','Bztavian','Chapuli','Chigoe',
            'Crawler','Diremite','Fly','Gnat','Ladybug','Mantid',
            'Mosquito','Scorpion','Spider','Twitherym','Wamoura','Wamouracampa'
        ) THEN 'Vermin'
        WHEN TRIM(TRAILING 'es' FROM TRIM(TRAILING 's' FROM family)) IN (
            'Belladonna','Flytrap','Funguar','Goobbue','Leafkin',
            'Mandragora','Morbol','Panopt','Rafflesia','Sabotender',
            'Sapling','Snapweed','Treant','Yggdreant'
        ) THEN 'Plantoid'
        WHEN TRIM(TRAILING 'es' FROM TRIM(TRAILING 's' FROM family)) IN (
            'Acuex','Botulus','Flan','Hecteyes','Leech','Plovid',
            'Sandworm','Slime','Slug','Worm'
        ) THEN 'Amorph'
        WHEN TRIM(TRAILING 'es' FROM TRIM(TRAILING 's' FROM family)) IN (
            'Amphiptere','Apkallu','Bat','Cockatrice','Colibri',
            'Flock Bat','Greater Bird','Harpeia','Hippogryph',
            'Lesser Bird','Tulfaires','Waktza'
        ) THEN 'Bird'
        WHEN TRIM(TRAILING 'es' FROM TRIM(TRAILING 's' FROM family)) IN (
            'Crab','Craklaw','Frog','Orobon','Pteraketos','Pugil',
            'Rockfin','Sea Monk','Ruszor','Uragnite'
        ) THEN 'Aquan'
        WHEN TRIM(TRAILING 'es' FROM TRIM(TRAILING 's' FROM family)) IN (
            'Corpselight','Corse','Doomed','Dullahan','Fomor',
            'Ghost','Hound','Naraka','Qutrub','Skeleton','Vampyr'
        ) THEN 'Undead'
        WHEN TRIM(TRAILING 'es' FROM TRIM(TRAILING 's' FROM family)) IN (
            'Acrolith','Bomb','Cardian','Caturae','Cluster','Detector',
            'Doll','Evil Weapon','Golem','Grimoire','Iron Giant',
            'Khimaira','Magic Pot','Marolith','Mimic','Snoll'
        ) THEN 'Arcana'
        WHEN TRIM(TRAILING 'es' FROM TRIM(TRAILING 's' FROM family)) IN (
            'Ahriman','Defiant','Demon','Dvergr','Gallu','Gargouille',
            'Imp','Macuil','Soulflayer','Taurus'
        ) THEN 'Demon'
        WHEN TRIM(TRAILING 'es' FROM TRIM(TRAILING 's' FROM family)) IN (
            'Dragon','Hydra','Puk','Wyrm','Wyvern','Wyvern (Dragoon Pet)',
            'Pet Wyvern','Zilant'
        ) THEN 'Dragon'
    END
);

这个逻辑会自动处理Bees→Bee、Flies→Fly、Bombs→Bomb这类常规复数形式，同时兼容本身就是单数的条目。

方法2：正则表达式匹配（适合支持正则的数据库） #

如果你的数据库支持正则表达式（比如MySQL的REGEXP、PostgreSQL的~），可以直接在CASE条件里匹配单数和对应的复数：

UPDATE monster
SET ecosystem = (
    CASE
        WHEN family REGEXP '^(Behemoth|Buffalo|Cehuetzi|Cerberus|Coeurl|Dhalmel|Gnole|Manticore|Marid|Opo-opo|Raaz|Rabbit|Ram|Sheep|Tiger|Yztarg)s?$' THEN 'Beast'
        WHEN family REGEXP '^(Adamantoise|Bugard|Eft|Gabbrath|Hill|Lizard|Matamata|Peiste|Raptor|Wivre)s?$' THEN 'Lizard'
        WHEN family REGEXP '^(Antlion|Bee|Beetle|Bztavian|Chapuli|Chigoe|Crawler|Diremite|Gnat|Ladybug|Mantid|Mosquito|Scorpion|Spider|Twitherym|Wamoura|Wamouracampa)s?$|^Fly(ies)?$' THEN 'Vermin'
        WHEN family REGEXP '^(Belladonna|Flytrap|Funguar|Goobbue|Leafkin|Mandragora|Morbol|Rafflesia|Sabotender|Sapling|Snapweed|Treant|Yggdreant)s?$|^Panopt(s)?$' THEN 'Plantoid'
        WHEN family REGEXP '^(Acuex|Botulus|Flan|Hecteyes|Leech|Plovid|Sandworm|Slime|Slug|Worm)s?$' THEN 'Amorph'
        WHEN family REGEXP '^(Amphiptere|Apkallu|Bat|Cockatrice|Colibri|Flock Bat|Greater Bird|Harpeia|Hippogryph|Lesser Bird|Tulfaires|Waktza)s?$' THEN 'Bird'
        WHEN family REGEXP '^(Crab|Craklaw|Frog|Orobon|Pteraketos|Pugil|Rockfin|Sea Monk|Ruszor|Uragnite)s?$' THEN 'Aquan'
        WHEN family REGEXP '^(Corpselight|Corse|Doomed|Dullahan|Fomor|Ghost|Hound|Naraka|Qutrub|Skeleton|Vampyr)s?$' THEN 'Undead'
        WHEN family REGEXP '^(Acrolith|Cardian|Caturae|Cluster|Detector|Doll|Evil Weapon|Golem|Grimoire|Iron Giant|Khimaira|Magic Pot|Marolith|Mimic|Snoll)s?$|^Bomb(s)?$' THEN 'Arcana'
        WHEN family REGEXP '^(Ahriman|Defiant|Dvergr|Gallu|Gargouille|Imp|Macuil|Soulflayer|Taurus)s?$|^Demon(s)?$' THEN 'Demon'
        WHEN family REGEXP '^(Dragon|Hydra|Puk|Wyrm|Zilant)s?$|^Wyvern(s)?$|^Pet Wyvern(s)?$|^Wyvern \(Dragoon Pet\)$' THEN 'Dragon'
    END
);

注意事项 #

1. 先测试再更新：建议先执行SELECT语句验证匹配结果，避免误更新：

   SELECT family,
       CASE
           -- 复制上方对应CASE逻辑
       END AS new_ecosystem
   FROM monster
   WHERE ecosystem IS NULL OR (CASE ... END) != ecosystem;
   

2. 特殊复数处理：如果遇到不规则复数（比如Sheep单复数同形），上述方法已经兼容，无需额外修改；如果有其他特殊规则，可以在字符串处理或正则中补充。

内容的提问来源于stack exchange，提问作者KoldKonfusion