Why the \G modifier in SELECT * FROM table_name\G?

MySQLMySQLi Database

The \G modifier gets the result in vertical order. If you use \g modifier, then it won’t affect the result. The \g works likesemi-colon.

Let us first create a table. The query to create a table is as follows:

mysql> create table DemoOfVertical
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> Name varchar(20),
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (3.40 sec)

Now you can insert some records in the table using insert command. The query is as follows:

mysql> insert into DemoOfVertical(Name) values('Bob');
Query OK, 1 row affected (0.18 sec)

mysql> insert into DemoOfVertical(Name) values('Carol');
Query OK, 1 row affected (0.19 sec)

mysql> insert into DemoOfVertical(Name) values('David');
Query OK, 1 row affected (0.14 sec)

mysql> insert into DemoOfVertical(Name) values('Mike');
Query OK, 1 row affected (0.17 sec)

Display all records from the table using select statement. In this semicolon(;) and \g will act as a statement terminator.

Case 1: Semicolon as a statement terminator. The query is as follows:

mysql> select *from DemoOfVertical;

The following is the output:

+----+-------+
| Id | Name  |
+----+-------+
| 1  | Bob   |
| 2  | Carol |
| 3  | David |
| 4  | Mike  |
+----+-------+
4 rows in set (0.00 sec)

Case 2: A \g is also a statement terminator. The query is as follows:

mysql> select *from DemoOfVertical\g

The following is the output:

+----+-------+
| Id | Name  |
+----+-------+
|  1 | Bob   |
|  2 | Carol |
|  3 | David |
|  4 | Mike  |
+----+-------+
4 rows in set (0.00 sec)

Case 3: If you want the result in vertical order, then use \G. The query is as follows:

mysql> select *from DemoOfVertical\G

The following is the output:

*************************** 1. row ***************************
Id: 1
Name: Bob
*************************** 2. row ***************************
Id: 2
Name: Carol
*************************** 3. row ***************************
Id: 3
Name: David
*************************** 4. row ***************************
Id: 4
Name: Mike
4 rows in set (0.00 sec)
raja
Published on 01-Feb-2019 12:03:49
Advertisements