MySQL图书登记报告查询：带前缀varchar类型barcode WHERE子句失效修复

问题出在CAST(oo.barcode AS UNSIGNED)：你的条码是HYD+数字的字符串，直接转无符号整数时，MySQL会从左到右读取数字，遇到非数字字符（这里的H）就停止，结果全转成0，导致WHERE条件完全失效。

修复方案 #

因为条码前缀HYD是固定3位，直接截取前缀后的数字部分再转成数值进行范围比较即可，同时可以加个过滤条件确保只处理格式正确的条码：

修复后的完整查询 #

SELECT oo.dateaccessioned AS 'Date',
    oo.barcode AS 'Acc. No.',
    ooo.title AS 'Title',
    ooo.author AS 'Author/Editor',
    concat_ws(' , ', o.editionstatement, oo.enumchron) AS 'Ed./Vol.',
    concat_ws(' ', o.place, o.publishercode) AS 'Place & Publisher',
    ooo.copyrightdate AS 'Year', o.pages AS 'Page(s)',
    ooooooo.name AS 'Source',
    oo.itemcallnumber AS 'Class No./Book No.',
    concat_ws(', ₹', concat(' ', ooooo.symbol, oooo.listprice), oooo.rrp_tax_included) AS 'Cost',
    concat_ws(' , ', oooooo.invoicenumber, oooooo.shipmentdate) AS 'Bill No. & Date',
    '' AS 'Withdrawn Date',
    '' AS 'Remarks'
FROM biblioitems o
LEFT JOIN items oo ON oo.biblioitemnumber=o.biblioitemnumber
LEFT JOIN biblio ooo ON ooo.biblionumber=o.biblionumber
LEFT JOIN aqorders oooo ON oooo.biblionumber=o.biblionumber
LEFT JOIN currency ooooo ON ooooo.currency=oooo.currency
LEFT JOIN aqinvoices oooooo ON oooooo.booksellerid=oo.booksellerid
LEFT JOIN aqbooksellers ooooooo ON ooooooo.id=oo.booksellerid
-- 核心修改部分
WHERE oo.barcode LIKE 'HYD%' 
  AND CAST(SUBSTRING(oo.barcode, 4) AS UNSIGNED) BETWEEN <<Accession Number>> AND <<To Accession Number>>
GROUP BY oo.barcode
ORDER BY oo.barcode ASC

关键修改说明 #

• oo.barcode LIKE 'HYD%'：先过滤出格式正确的条码，避免处理非HYD开头的字符串导致转换错误
• SUBSTRING(oo.barcode, 4)：从第4个字符开始截取，去掉前3位的HYD前缀
• 再将截取后的数字字符串转成无符号整数进行范围比较，这样就能正确匹配条码的数字部分区间

注意：使用时，<<Accession Number>>和<<To Accession Number>>需要填写不带HYD前缀的纯数字，比如要筛选HYD100到HYD200，就填100和200。

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