mysql字段中有空格问题查询汇总-保姆级教程

今天遇到一个数据库记录空格问题,查了一些资料,汇总一下

背景:数据库是mysql,版本:5.7.35

1.建表并初始化数据

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_user
-- ----------------------------
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user`  (
  `id` int(11) NOT NULL,
  `age` int(11) NULL DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `birthday` date NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t_user
-- ----------------------------
INSERT INTO `t_user` VALUES (1, 1, '张三:1:1', '1', '2022-01-31');
INSERT INTO `t_user` VALUES (2, 2, '张三22', '1', NULL);
INSERT INTO `t_user` VALUES (3, 12, '李四666', '2', NULL);
INSERT INTO `t_user` VALUES (4, 13, '李四66', '2', NULL);
INSERT INTO `t_user` VALUES (5, 15, '李四6', '2', NULL);

# 左边有空格
INSERT INTO `t_user` VALUES (7, 12, ' 王五888888 ', '3', NULL);

# 右边有空格
INSERT INTO `t_user` VALUES (8, 13, '王五8888 ', '3', NULL);

# 两边有空格
INSERT INTO `t_user` VALUES (9, 11, ' 王五88888888 ', '3', NULL);
INSERT INTO `t_user` VALUES (100, 1, '1', '1', '2021-05-01');

SET FOREIGN_KEY_CHECKS = 1;

ps:重点是红色的数据,有空格

2.等号查询

2.1等号查询,右边有空格,可以查出来

select * from t_user where name = "王五8888";

结果:

2.2等号查询,左边有空格,查不出来

select * from t_user where name = "王五888888";

结果:

2.3等号查询,左右两边都有空格,查不出来

select * from t_user where name = "王五88888888";

结果:

3.like查询,不管左边还是右边有空格,都可以查询出来

select * from t_user where name like "%王五%";

结果:

4.中间空格,不影响,可以查出来

5.空格替换

替换左边空格

field_name =  ltrim(field_name)

示例:

替换右边空格

field_name = rtrim(field_name)

示例:

替换两边空格

field_name =  trim(field_name)