MySQL Commands

Some useful MySQL commands:

Alter a table to create a fulltext index of the columns specified..

ALTER TABLE tablename ADD FULLTEXT(column1, column2);

Search a fulltext index, using query expansion which increases relevancy but decreases the speed of the query. Further speed up the query by limiting the result set to 10 results.

SELECT * FROM tablename WHERE MATCH(column1,column2,column3) AGAINST('Search Phrase' WITH QUERY EXPANSION) LIMIT 10;

To import data from a text file into a MySQL database, assuming the text file is a list with each new record on a new line. For example:

Number One
Thing number 2
and a Third

Use the following MySQL command:

LOAD DATA INFILE '/home/user/Desktop/file.txt' INTO TABLE tablename (column1);

This import will only work on a database that is already setup with the proper column settings and data type.


Execute MySQL query from a bash shell script:

#!/bin/bash
TABLE_NAME=testtable
USER_NAME=root
IP_ADDR=localhost
PASSWD=Secret

somevar=`echo "select column1 from testtable where idkey='5'" | mysql -h $IP_ADDR -u $USER_NAME -p$PASSWD $TABLE_NAME `

echo $somevar

Notes on the above bash script: You can replace the MySQL query in the script with any MySQL command.

Dont forget about the ticks ( ` ` ) that encapsulate the commands inside. This is a useful trick that took me a while to research and figure out. I wanted to set a variable to the results of some commands and found that you need to use the ` ` , not the ' (single quote) this tick shares the same key as the tilde key, to the left of the 1 key.



To view any query that may be hung up or taking a long time to execute:

show full processlist

This will display a table of the MySQL processes running, the process ID, and the Status.

If you find a rogue or otherwise not needed query you can kill that process ID using:

kill 1234

Where 1234 is the process ID that you want to end.



More coming soon.