Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: varchar和bigint 关联输出错误
|
username: TiDBer_TxSpaZ25
[TiDB Usage Environment] Production Environment / Testing / PoC
When varchar and bigint are associated, if the field length exceeds more than 10 digits, an association exception occurs. Besides modifying the table structure and SQL conversion, is there any parameter that can control this phenomenon?
The data with id = 2 is associated with abnormal data.
Can you send the reproduction script in text form? Typing it out from the screenshot is too troublesome~
Bro is here
|
username: show CREATE table test.t1
CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
INSERT INTO `t1` (`id`, `name`) VALUES (1, '123456789012345611');
INSERT INTO `t1` (`id`, `name`) VALUES (2, '123456789012345612');
INSERT INTO `t1` (`id`, `name`) VALUES (3, '123123');
show CREATE table test.t2
CREATE TABLE `t2` (
`id` int(11) NOT NULL,
`name` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
INSERT INTO `t2` (`id`, `name`) VALUES (1, 123456789012345611);
INSERT INTO `t2` (`id`, `name`) VALUES (2, 123456789012345611);
INSERT INTO `t2` (`id`, `name`) VALUES (3, 123123);
select t1.*,t2.* from test.t1 ,test.t2 where t1.name =t2.name
Looking at the execution plan, it should be caused by the conversion here.
Issues have already been raised. It looks like the performance here is consistent with MySQL.
Apart from changing the table structure and manually converting types, I haven’t seen any better solutions.
You should try to avoid letting the database perform implicit conversions, as it can lead to not using indexes, low efficiency, and various problems.
Type coercion.
Implicit conversion in the database will result in an error if it cannot be converted.
Implicit conversions should be avoided in all circumstances.
Using cast is a temporary solution, and it would definitely not be easy to change the SQL for online business. Since the optimizer has already done the conversion, it indicates a difference in data types. However, there is some loss after conversion. Ultimately, it is the product that needs optimization and improvement.
MySQL should also have this problem, it shouldn’t be something encountered only after switching to TiDB.
MySQL Query Precision Loss, Implicit Type Conversion Between varchar and bigint!_mysql varchar to bigint-CSDN Blog
Originally, I didn’t want to say this, but MySQL is not considered a competitor to TiDB. However, why does a certain competitor have no issues?
It’s not difficult, just execute the original poster’s statement and it can be reproduced.
Are you sure there’s no problem? I just tested it with MySQL 8.
The issues clearly indicate that TiDB’s performance is consistent with MySQL’s performance. This indirectly confirms that it perfectly supports MySQL’s syntax. 
That definitely means taking the essence and discarding the dross.
|
username: 
|
username: 
|
username: 
|
username: 
|
username: