Concat() of combined fields in MySQL

1. Introduction
2. Main text

2.1 Field concatenation
2.2 Performing Arithmetic Calculations on Fields

1. Introduction

MySQL is a relational database. When we use it, we often map the attributes of objects into columns and store them in tables. Therefore, the query results are also separate attributes without any processing. If we want to query the result set returned in MySQL and return the values of multiple fields (columns) together or after specific calculations, we can use the field calculation function provided by MySQL .

The following two types are often used in field calculations:

Field concatenation
Fields that perform arithmetic calculations

2. Main text

All field combinations implemented in MySQL can be completed in the client, but implementing field combinations directly in the MySQL server will be faster than the client.

2.1 Field concatenation

Prepare a user table and insert several records as follows:

SET NAMES utf8mb4;

SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------

-- Table structure for user

-- ----------------------------

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Primary key',

  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'User name',

  `nation` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Ethnicity',

  PRIMARY KEY (`id`) USING BTREE

) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of user

-- ----------------------------

INSERT INTO `user` VALUES (1, '李子八', '汉族');

INSERT INTO `user` VALUES (2, '张三', '慧族');

INSERT INTO `user` VALUES (3, '李四', 'Uyghur');

INSERT INTO `user` VALUES (4, '王五', '蒙古');

SET FOREIGN_KEY_CHECKS = 1;

need:

Get the user's name and ethnic combination information

Statement:

mysql> select concat(name, '（',nation, '）') from user;

+---------------------------------+

| concat(name, '（',nation, '）') |

+---------------------------------+

| Li Ziba (Han nationality) |

| Zhang San (Hui) |

| Li Si (Uyghur) |

| Wang Wu (Mongolian) |

+---------------------------------+

Analysis:

The concat() function is used here. Any number of elements can be combined in the function. These elements can be table fields, fixed characters, etc. The elements are separated by , and the order of combination is the same as the order of characters in concat() function.

What about the field name after combination?

Careful friends found that the field name after combination uses the entire function body of concat() function. Obviously, this display is not what we want! If you want to specify the field name you want, just use an alias!

mysql> select concat(name, '（',nation, '）') as user_message from user;

+------------------+

| user_message |

+------------------+

| Li Ziba (Han nationality) |

| Zhang San (Hui) |

| Li Si (Uyghur) |

| Wang Wu (Mongolian) |

+------------------+

The usage of an alias is to use as , followed by the field name you want to specify.

2.2 Performing Arithmetic Calculations on Fields

When combining fields, we often do more than simply concatenate strings. It may involve arithmetic calculations between fields. In this case, we need to use arithmetic operators in MySQL .

MySQL provides addition, subtraction, multiplication and division operators as follows:

Operators	illustrate
+	add
-	reduce
*	take
/

Prepare a product table and insert several records as follows:

SET NAMES utf8mb4;

SET FOREIGN_KEY_CHECKS = 0;



-- ----------------------------

-- Table structure for product

-- ----------------------------

DROP TABLE IF EXISTS `product`;

CREATE TABLE `product` (

  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary key',

  `product_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'Product name',

  `price` decimal(10, 2) UNSIGNED NOT NULL COMMENT 'Product price',

  `number` int(11) NOT NULL COMMENT 'Product quantity',

  PRIMARY KEY (`id`) USING BTREE

) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of product

-- ----------------------------

INSERT INTO `product` VALUES (1, 'Apple iPhone 13 (A2634)', 6799.00, 22);

INSERT INTO `product` VALUES (2, 'HUAWEI P50 Pro', 6488.00, 88);

INSERT INTO `product` VALUES (3, 'MIX4', 4999.00, 30);

INSERT INTO `product` VALUES (4, 'OPPO Find X3', 3999.00, 15);

INSERT INTO `product` VALUES (5, 'vivo X70 Pro+', 5999.00, 27);

SET FOREIGN_KEY_CHECKS = 1;

need:

Query the total value of current inventory products

Statement:

mysql> select product_name, concat(price * number) as gross_value from product;

+-------------------------+-------------+

| product_name | gross_value |

+-------------------------+-------------+

| Apple iPhone 13 (A2634) | 149578.00 |

| HUAWEI P50 Pro | 570944.00 |

| MIX4 | 149970.00 |

| OPPO Find X3 | 59985.00 |

| vivo X70 Pro+ | 161973.00 |

+-------------------------+-------------+

Operator order problem:

Operators in MySQL also have an order, which is the same as the order of operation of ordinary operators, (* / ) > (+ -) . When using combined operators, you must pay attention to the order of operators. Reasonable use of () can constrain the execution order of operators.

Example:

mysql> select concat(12 - 3 * 4);

+--------------------+

| concat(12 - 3 * 4) |

+--------------------+

| 0 |

+--------------------+

1 row in set (0.00 sec)

mysql> select concat((12 - 3) * 4);

+----------------------+

| concat((12 - 3) * 4) |

+----------------------+

| 36 |

+----------------------+

1 row in set (0.00 sec)

It is worth noting that in MySQL , if the dividend is 0, no exception will be thrown, but NULL will be returned. This is because MySQL handles the operation abnormally.

mysql> select concat(12 / 0);

+----------------+

| concat(12 / 0) |

+----------------+

| NULL |

+----------------+

1 row in set, 1 warning (0.00 sec)

This is the end of this article about concat() in MySQL. For more information about concat() in MySQL, please search previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!

You may also be interested in:

Nested use of insert and select in MySQL solves the problem of inserting combined fields
MySQL merges multiple rows of data based on the group_concat() function
A brief discussion on the sorting method of the group_concat() function in MySQL
Summary of mysql group_concat() function usage
mysql uses group_concat() to merge multiple rows of data into one row
How to use the Mysql GROUP_CONCAT() function

<<: Summary of standard usage of html, css and js comments

>>: Ubuntu compiles kernel modules, and the content is reflected in the system log