Tag: mysql

MySQL Client Format Output to Space-Delimited

Post author By sskaje
Post date May 31, 2017
No Comments on MySQL Client Format Output to Space-Delimited

By @sskaje
Link: https://sskaje.me/2017/05/mysql-client-format-output-space-delimited/

Traditionally:

mysql> select * from INNODB_SYS_COLUMNS limit 3;
+----------+----------+-----+-------+--------+-----+
| TABLE_ID | NAME     | POS | MTYPE | PRTYPE | LEN |
+----------+----------+-----+-------+--------+-----+
|       11 | ID       |   0 |     1 | 524292 |   0 |
|       11 | FOR_NAME |   1 |     1 | 524292 |   0 |
|       11 | REF_NAME |   2 |     1 | 524292 |   0 |
+----------+----------+-----+-------+--------+-----+

mysql> select * from INNODB_SYS_COLUMNS limit 3;

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

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

| 11 | ID | 0 | 1 | 524292 | 0 |

| 11 | FOR_NAME | 1 | 1 | 524292 | 0 |

| 11 | REF_NAME | 2 | 1 | 524292 | 0 |

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

To save output:

mysql> pager cat > /tmp/output
PAGER set to 'cat > /tmp/output'
mysql> select * from INNODB_SYS_COLUMNS limit 3;
3 rows in set (0.00 sec)

mysql> pager cat > /tmp/output

PAGER set to 'cat > /tmp/output'

mysql> select * from INNODB_SYS_COLUMNS limit 3;

3 rows in set (0.00 sec)

# cat /tmp/output 
+----------+----------+-----+-------+--------+-----+
| TABLE_ID | NAME     | POS | MTYPE | PRTYPE | LEN |
+----------+----------+-----+-------+--------+-----+
|       11 | ID       |   0 |     1 | 524292 |   0 |
|       11 | FOR_NAME |   1 |     1 | 524292 |   0 |
|       11 | REF_NAME |   2 |     1 | 524292 |   0 |
+----------+----------+-----+-------+--------+-----+

# cat /tmp/output

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

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

| 11 | ID | 0 | 1 | 524292 | 0 |

| 11 | FOR_NAME | 1 | 1 | 524292 | 0 |

| 11 | REF_NAME | 2 | 1 | 524292 | 0 |

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

To format output to space delimited:

mysql> pager grep -Ev '^\+-' | column -t -s '|'                                                                                                                                                                                               PAGER set to 'grep -Ev '^\+-' | column -t -s '|''
mysql> select * from INNODB_SYS_COLUMNS limit 3;
 TABLE_ID    NAME        POS    MTYPE    PRTYPE    LEN 
       11    ID            0        1    524292      0 
       11    FOR_NAME      1        1    524292      0 
       11    REF_NAME      2        1    524292      0 
3 rows in set (0.01 sec)

mysql> pager grep -Ev '^\+-' | column -t -s '|' PAGER set to 'grep -Ev '^\+-' | column -t -s '|''

mysql> select * from INNODB_SYS_COLUMNS limit 3;

TABLE_ID NAME POS MTYPE PRTYPE LEN

11 ID 0 1 524292 0

11 FOR_NAME 1 1 524292 0

11 REF_NAME 2 1 524292 0

3 rows in set (0.01 sec)

I found CentOS 7 and Ubuntu 16.04 uses different column commands,
in CentOS 7, column is from util-linux, in Ubuntu 16.04 and OSX, BSD like.

I tried to format output as TSV, but failed.

MySQL Client Format Output to Space-Delimited by @sskaje: https://sskaje.me/2017/05/mysql-client-format-output-space-delimited/

Incoming search terms:

for example if the two guards that were paired started with 3 and 5 bananas after the first round of thumb wrestling they will have 6 and 2 (the one with 3 bananas wins and gets 3 bananas from the loser) after the second round they will have 4 and 4 (the

Tags mysql, mysql pager, pager

Linux 操作系统相关杂七杂八

Debian Reset MySQL Root Password

Post author By sskaje
Post date June 15, 2015
No Comments on Debian Reset MySQL Root Password

By @sskaje
Link: https://sskaje.me/2015/06/debian-reset-mysql-root-password/

Only for mysql server installed by ‘apt-get‘.

Under System root and localhost only.

# cat /etc/mysql/debian.cnf

1	# cat /etc/mysql/debian.cnf

You can find password for ‘debian-sys-maint‘, you can update root password by logging in as this user.

If you have replication, you may need to update all slaves manually.

Debian Reset MySQL Root Password by @sskaje: https://sskaje.me/2015/06/debian-reset-mysql-root-password/

Tags mysql, mysql password, mysql root, mysql root password, password, root, root password

Hadoop相关 Hive Impala PrestoDB 学习研究

MySQL/Hive/Presto/Impala Transposition

Post author By sskaje
Post date February 18, 2014
No Comments on MySQL/Hive/Presto/Impala Transposition

By @sskaje
Link: https://sskaje.me/2014/02/mysql-hive-presto-impala-transposition/

Rows to Columns

Rows to Comma Separated String

FROM:
+-----+-----+
| uid | tag |
+-----+-----+
|   1 |   1 |
|   1 |   2 |
|   1 |   3 |
|   2 |   1 |
|   2 |   4 |
|   3 |   5 |
+-----+-----+
TO:
+-----+----------+
| uid | tag_list |
+-----+----------+
|   1 | 1,2,3    |
|   2 | 1,4      |
|   3 | 5        |
+-----+----------+

FROM:

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

| uid | tag |

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

| 1 | 1 |

| 1 | 2 |

| 1 | 3 |

| 2 | 1 |

| 2 | 4 |

| 3 | 5 |

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

TO:

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

| uid | tag_list |

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

| 1 | 1,2,3 |

| 2 | 1,4 |

| 3 | 5 |

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

MySQL

Use GROUP_CONCAT(). This function also works in Infobright.

MySQL/Hive/Presto/Impala Transposition by @sskaje: https://sskaje.me/2014/02/mysql-hive-presto-impala-transposition/