MySQL 多個%等模糊查詢功能實現

目錄一、建表并插入數據1、創建一個people表2、向people表添加數據二、查詢語句一、建表并插入數據1、創建一個people表DROP TABLE IF EXISTS `people`;CREATE TABLE `people` ( `id` int NOT NULL COMMENT '主鍵', `name` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '姓名', `sex` tinyint NOT NULL COMMENT '性別', `age` int NOT NULL COMMENT '年齡', `phone` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '聯系方式', PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;2、向people表添加數據insert into people values(1,'測試人員',18,1,'13812345678');insert into people values(2,'測測人員',19,0,'13812345679');insert into people values(3,'人員測試',20,1,'13812345680');insert into people values(4,'測試人員1',21,0,'13812345681');insert into people values(5,'員人試測',22,1,'13812345682');二、查詢語句-- 1、模糊查詢（單個條件）select * from people where name like '%測%';

-- 2、模糊查詢（多個條件）select * from people where name like '%測%' and name like '%人%';

select * from people where name like '%測%' or name like '%人%';

------有趣的查詢（網頁端前端傳參%測試）------

-- 3、模糊查詢（順序執行）select * from people where name like '%測%人%';

select * from people where name like '%人%測%';

-- 4、_： 表示任意單個字符。匹配單個任意字符，它常用來限制表達式的字符長度語句select * from people where name like '測試人員_';

select * from people where name like '__人員';

注：mysql 通配符查詢必須用 rlike

-- 5、[ ]：表示括號內所列字符中的一個（類似正則表達式）。指定一個字符、字符串或范圍，要求所匹配對象為它們中的任一個。select * from people where name rlike '[試]人員';

-- 6、[^ ] ：表示不在括號所列之內的單個字符。其取值和 [] 相同，但它要求所匹配對象為指定字符以外的任一個字符 ^ 非。select * from people where name rlike '[^試]人員';

-- 7、^：以xx開頭的記錄select * from people where name rlike '^測';

-- 8、$：以xx結尾的記錄select * from people where name rlike '員$';

-- 9、.：任意單個的select * from people where name rlike '.人員';

到此這篇關于MySQL 多個%等模糊查詢的文章就介紹到這了,更多相關mysql模糊查詢內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網！