Method for comparing the size of varchar type numbers in MySQL database

Create a test table

-- ----------------------------
-- Table structure for check_test
-- ----------------------------
DROP TABLE IF EXISTS `check_test`;
CREATE TABLE `check_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `current_price` varchar(10) NOT NULL,
  `price` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
 
-- ----------------------------
-- Records of check_test
-- ----------------------------
INSERT INTO `check_test` VALUES ('1', '12', '14');
INSERT INTO `check_test` VALUES ('2', '22', '33');
INSERT INTO `check_test` VALUES ('3', '15', '8');
INSERT INTO `check_test` VALUES ('4', '9', '7');
INSERT INTO `check_test` VALUES ('5', '12', '12.9'); 

We execute the following SQL:

-- Find the records where current_price is smaller than price select * from check_test where current_price<price;
select * from check_test where (current_price+0)<(price+0);
 
-- Find the records where current_price is greater than price select * from check_test where current_price>price;
select * from check_test where (current_price+0)>(price+0);

The results are as follows:

We can see that in result 1 , 15>8 does not meet the condition but is found, while in result 3, 15>8 meets the condition but is not found.

Why is this? Because the comparison of string type numbers starts from the first digit and compares the corresponding value in the character code. If they are equal, continue to compare the second digit. If they are not equal, the larger value is compared . The ASCII code values ​​corresponding to the numeric characters are as follows:

For example:

• If current_price="12", price="14", the ASCII decimal value of the first character "1" is 49, which is equal to each other. The ASCII decimal value of the second character "2" is 50, and the ASCII decimal value of "4" is 52. 50<52, so the result is correct.
• If current_price="15", price="8", the ASCII decimal value corresponding to the first character "1" in current_price is 49, and the ASCII decimal value corresponding to the first character "8" in price is 56, 49<56, so it can be queried, but it is not the result we expect.

We can verify this through code, using JavaScript:

var current_price = "12";
var price = "14";
console.log(current_price < price); // true
 
var current_price = "15";
var price = "8";
console.log(current_price < price); // true

Therefore, numbers that may need to be compared should not be set to varchar type. However, if they have been set to varchar type, then if you want to compare them, convert them to numeric type for comparison and add 0 to the field in SQL.

If it is a programming language, it needs to be converted through the corresponding function, and cannot be converted directly by adding 0, such as JavaScript:

var current_price = "12";
var price = "14";
console.log(parseInt(current_price) < parseInt(price)); // true
 
var current_price = "15";
var price = "8";
console.log(parseInt(current_price) < parseInt(price)); // false

Note: The decimal value corresponding to the character is related to its encoding, but the ASCII code is compatible.

Reference Links:

Comparison of numbers of string type

Comparison of VARCHAR type strings

Comparing the size of numbers of varchar type

This is the end of this article about how to compare the size of varchar numbers in MySQL database. For more information about comparing the size of MySQL varchar numbers, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!