Char data type expression is not compatible with MYSQL 8.0

## Bug Report
May I ask why the expression ' 'reports an error, however the expression ! ' ' is right ?
It is not compatible with MYSQL 8.0.
Thank for your reply.
Please answer these questions before submitting your issue. Thanks!

### 1. Minimal reproduce step (Required)

```SQL
CREATE TABLE t0(c0 BLOB , c1 FLOAT UNIQUE );
INSERT INTO t0(c0, c1) VALUES ('abc', 0.123);
INSERT INTO t0(c0, c1) VALUES ('efg', 0.456);

UPDATE t0 SET c0='xyz' WHERE ' ';
UPDATE t0 SET c0='xyz' WHERE !' ';
```
### 2. What did you expect to see? (Required)
In MySQL
```SQL
mysql> UPDATE t0 SET c0='xyz' WHERE ' ';
Query OK, 0 rows affected (0.01 sec)
Rows matched: 0 Changed: 0 Warnings: 0

mysql> UPDATE t0 SET c0='xyz' WHERE !' ';
Query OK, 2 rows affected, 1 warning (0.02 sec)
Rows matched: 2 Changed: 2 Warnings: 1
```
### 3. What did you see instead (Required)
```SQL
mysql> UPDATE t0 SET c0='xyz' WHERE ' ';
ERROR 1292 (22007): Truncated incorrect DOUBLE value: ''
mysql> UPDATE t0 SET c0='xyz' WHERE !' ';
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0
```
### 4. What is your TiDB version? (Required)

Release Version: v8.1.0
Edition: Community
Git Commit Hash: 945d07c5d5c7a1ae212f6013adfb187f2de24b23
Git Branch: HEAD
UTC Build Time: 2024-05-21 03:51:57
GoVersion: go1.21.10
Race Enabled: false
Check Table Before Drop: false
Store: tikv

Start by running the supplied CREATE TABLE, INSERT, and UPDATE statements on TiDB v8.1.0 and MySQL 8.0, comparing the results and warnings for the two WHERE expressions. Done means the TiDB behavior matches the documented MySQL results without the incorrect truncation error.

Written by the indexing model from the issue text.