| | How to convert query resutls to XML
You can display SQL query results in XML format when running mysql from command line:
$ c:/mysql/bin/mysql -u [user_name] -p [database_name] -X -e "SELECT * FROM forums_phpbb_config"
Enter password: ******
config_id
... | | More...
10/26/06 | | | | |
|
| | How to print SQL query results vertically
In MySQL you can display the query results vertically, so that each column will be shown on a new line with \G switch. Fox example:
mysql> select * from nuke_phpbb_config \G
*************************** 1. row ***************************
config_name: config_id
config_value: 1
*************************** 2. row ***************************
config_name: board_disable
config_value: 0
*************************** 3. row ***************************
config_name: sitename
config_value: yourdomain.com
*************************** 4. row ... | | More...
10/26/06 | | | | |
|
| | How to check the version of MySQL server
To find out the version of MySQL server using command line on localhost run the following:
mysql -V
To check the version on host "myhost" run:
mysql -h myhost -V
You can also see the server version in phpMyAdmin, it will be displayed on the front page.
| | More...
10/25/06 | | | | |
|
| | How to delete a column
To delete a column from a MySQL table run the following command:
mysql> ALTER TABLE 'table_name' DROP 'col_name';
| | More...
10/25/06 | | | | |
|
| | How to save query results in a text file
Here's how to put MySQL query results in an ASCII file i.e. tab delimited or CSV file.
mysql> SELECT field1, field2 FROM tab1 INTO OUTFILE 'logfile.txt';
By default fields will be separated by tab, but you can change it using the following command:
mysql> SELECT field1, field2 FROM tab1
... | | More...
10/19/06 | | | | |
|
| | How to rename MySQL table
It's quite easy to change a name of MySQL table and the syntax is similar to that of other databases. Of course you'd have to make sure the table name is not hardcoded somewhere i.e. in a php script. Just run this command:
mysql> RENAME TABLE tab_a TO tab_b;
| | More...
10/19/06 | | | | |
|
| | Find duplicate records query
Use the following SQL statement for finding duplicates in a table. I think this should work for any relational database, not only MySQL.
SELECT col_name, COUNT(col_name) AS num
FROM table_name
GROUP BY col_name
HAVING (COUNT(col_name) > 1);
| |  
More...
02/16/06 | | | | |
|
|
| | How to purge MySQL binary logs
You can put this command in crontab to run at regular intervals. This will purge database binary logs. $ designates shell prompt, replace passwd with the actual password.
$ /bin/echo 'reset master' | /usr/local/mysql/bin/mysql --password=passwd
| | More...
02/08/06 | | | | |
|
|
