MySQL App Little Notes

MySQL hangs the Debugs

1. Attaching to MySQL

[Email protected]: gdb-p 1232

1232 is the PID of MySQL

2. Show current thread

(GDB) Info threads

3. Show backtracking for all threads

(GDB) thread apply all BT

MySQL BINARY

Mysql> SELECT * from tbl_4;+----+-------+| ID | Title |+----+-------+|  1 | One    | |  2 |    | |  3 |    | |  4 | AA    | |  5 | bb    | |  6 | CC    | |  7 | AA    | |  8 | BB    | |  9 | CC    |+----+-------+9 rows in Set (0.00 sec) mysql> SELECT * from tbl_4 WHERE title like \ ' a%\ '; +----+-------+| ID | Title |+----+-------+|  4 | AA    | |  7 | AA    |+----+-------+2 rows in Set (0.00 sec)

Mysql> SELECT * from Tbl_4 WHERE BINARY title like \ ' a%\ '; +----+-------+| ID | Title |+----+-------+|  4 | AA    |+----+-------+1 row in Set (0.00 sec) mysql> SELECT * from Tbl_4 WHERE BINARY title like \ ' a%\ '; +----+-------+| ID | Title |+----+-------+|  7 | AA    |+----+-------+1 row in Set (0.00 sec)

GROUP by with ROLLUP

In the last row of the GROUP by result, list the sum of all the rows, as follows

Mysql> S elect COUNT (*), title from Tbl_4 GROUP by title with rollup;+----------+-------+| COUNT (*) | Title |+----------+-------+|        1 | One    | |        1 |    | |        1 |    | |        2 | AA    | |        2 | bb    | |        2 | CC    | |        9 | NULL  |+----------+-------+7 rows in Set (0.00 sec)

ORDER by IF

Sort a column with a specific value at the top, for example, in the following order to place the AA in front:

Mysql> SELECT title from Tbl_4 ORDER by IF (title = \ ' Aa\ ', 0, 1), title;+-------+| Title |+-------+| AA    | | AA | | | | | | | | |    | bb    | | BB    | | cc    | | CC    |+-------+9 rows in Set (0.00 sec)

Prompt

There are a lot of MySQL database management, sometimes it happens that you forget to use prompt to rewrite the MySQL client prompt on that server's database.

Mysql>prompt \\[email protected]\\h (\\d) \\r:\\m:\\s>

• \\u Connecting Users
• \\h Connecting the Host
• \\d Connection Database
• \\r:\\m:\\s: Show Current time

One way to do this is to configure it directly in the MY.CNF.

#不是 [Mysqld][mysql]prompt=\\\\[email protected]\\\\d \\\\r:\\\\m>

Pager

If the SELECT has more than a few screens out of the result set, the previous result cannot be seen. Using pager, you can set more or less to call the OS to display the results of the query, and use more or less to view large files in the OS.

Mysql> Pager Morepager set to \ ' more\ ' mysql> \\p morepager set to \ ' more\ ' mysql> pager lesspager set to \ ' Less\ ' m Ysql> \\p Lesspager set to \ ' Less\ ' mysql> Nopagerpager set to stdout

DELIMITER

DELIMITER is telling the MySQL interpreter what the Terminator of the command is.

By default, the end of the MySQL command is a semicolon (;), in the case of a write process or function, which can cause a lot of problems, because there are many statements in the stored procedure, so each one requires a semicolon. So you need to choose a string that is unlikely to appear in your statement or program as a delimiter.

[Email protected] (test) 03:27:17>delimiter $

[Email protected] (test) 03:27:24>select * from tbl_4$

+―-+――-+

| ID | Title |

+―-+――-+

| 1 | 11 |

| 2 | 22 |

| 3 | 33 |

| 4 | AA |

| 5 | bb |

| 6 | CC |

| 7 | AA |

| 8 | BB |

| 9 | CC |

+―-+――-+

9 Rows in Set (0.00 sec)

LOAD DATA LOCAL INFILE

LOAD DATA [Low_priority | CONCURRENT] [LOCAL] INFILE \ ' file_name.txt\ '

Load data INFILE and load data LOCAL INFILE

In the database, both the load data INFILE and the load data local INFILE can be imported locally, while the MySQL5.0 version supports the above mode by default

mysql> load Data INFILE ‘test.sql ' into table test;mysql> load data LOCAL INFILE ' test.sql ' into table tes T

Start MySQL plus parameters to restrict the use of the LOAD DATA LOCAL INFILE

[Email protected]:/usr/local/mysql/bin/mysqld_safe--local-infile=0 &

mysql> LOAD DATA INFILE ' test.sql ' into TABLE test;

ERROR 1148 (42000): The used command is not allowed with this MySQL version

For load DATA INFILE, file access on the server host can be controlled by the user's File_priv

Run the application at the MySQL prompt

mysql>\\! Cd/home

• This article is from: Linux Tutorial Network

MySQL App Little Notes