要通过在MySQL中传递名称来获取域名,可以使用substring_index()。让我们首先创建一个表-mysql> create table DemoTable ( UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, UserMailId varchar(200) );Query OK, 0 rows affected (0.77 sec)
使用插入命令在表中插入一些记录-mysql> insert into DemoTable(UserMailId) values('John9989@facebook.com');Query OK, 1 row affected (0.18 sec)mysql> insert into DemoTable(UserMailId) values('983773CS@yahoo.com');Query OK, 1 row affected (0.23 sec)mysql> insert into DemoTable(UserMailId) values('Chris95@gmail.com');Query OK, 1 row affected (0.20 sec)
使用select语句显示表中的所有记录-mysql> select *from DemoTable;
这将产生以下输出-+--------+-----------------------+
| UserId | UserMailId |
+--------+-----------------------+
| 1 | John9989@facebook.com |
| 2 | 983773CS@yahoo.com |
| 3 | Chris95@gmail.com |
+--------+-----------------------+
3 rows in set (0.00 sec)
以下是通过在MySQL中传递名称来获取域名的查询。mysql> select UserId,UserMailId, substring_index(substring_index(UserMailId, '@', -1), '.', 1) AS `Domain_Name` from DemoTable;
这将产生以下输出。在这里,获取域名-+--------+-----------------------+-------------+
| UserId | UserMailId | Domain_Name |
+--------+-----------------------+-------------+
| 1 | John9989@facebook.com | facebook |
| 2 | 983773CS@yahoo.com | yahoo |
| 3 | Chris95@gmail.com | gmail |
+--------+-----------------------+-------------+
3 rows in set (0.01 sec)