MySQL-用来自同一行中另一列的子字符串填充一列

问题描述

我想遍历整个表，用另一个列的值的子字符串填充新创建的列的值.

I would like to run over an entire table, populating the value of a newly created column with a substring of a value from another column.

给出与以下内容相似的表结构:

Given a table structure not unlike the following:

+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| id     | int(11)      | NO   | PRI | NULL    | auto_increment |
| email  | varchar(150) | YES  |     | NULL    |                |
| domain | varchar(100) | YES  |     | NULL    |                |
+--------+--------------+------+-----+---------+----------------+

其中包含类似以下数据:

Which contains data resembling:

+----+-------------------------+--------+
| id | email                   | domain |
+----+-------------------------+--------+
|  1 | [email protected]         | NULL   |
|  2 | [email protected]         | NULL   |
|  3 | [email protected]       | NULL   |
|  4 | [email protected] | NULL   |
|  5 | [email protected] | NULL   |
|  6 | [email protected]      | NULL   |
+----+-------------------------+--------+

我想查询一个解析电子邮件地址的域部分，并将其放在域列中，以得到如下结果:

I would like to have a query to parse the domain portion of the email address, and put it in the domain column, to end up with a result like:

+----+-------------------------+-------------------+
| id | email                   | domain            |
+----+-------------------------+-------------------+
|  1 | [email protected]         | domain1.com       |
|  2 | [email protected]         | domain1.com       |
|  3 | [email protected]       | domain1.com       |
|  4 | [email protected] | anotherdomain.com |
|  5 | [email protected] | anotherdomain.com |
|  6 | [email protected]      | thethird.com      |
+----+-------------------------+-------------------+

目前，我正在使用shell脚本在MySQL引擎的外部中进行此操作，但这效率很低，我敢肯定必须在MySQL引擎内部实现更好的方法

Currently, I am doing this outside of the MySQL engine with a shell script, but this is inefficient, and I'm sure there must be a better way to do it inside of the MySQL engine.

效率在这里很重要，因为我将在生产中使用的表是几万甚至数十万行.

Efficiency is important here, as the tables I will be doing this on in production are tens or even hundreds of thousands of rows.

推荐答案

您可以使用 SUBSTRING_INDEX :

SELECT
  id,
  email,
  SUBSTRING_INDEX(email, '@', -1) domain
FROM
  yourtable

或通过此操作更新您的数据:

or this to update your data:

UPDATE yourtable
SET domain = SUBSTRING_INDEX(email, '@', -1)

请在此处看到小提琴.

这篇关于MySQL-用来自同一行中另一列的子字符串填充一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！