Ubuntuland

Basic MySQL User Utility Commands

SE Syntax
USE db_name
The USE db_name statement tells MySQL to use the db_name database as the default
database for subsequent queries. The database remains current until the end of the session or until another USE statement is issued:
mysql> USE db1;
mysql> SELECT count(*) FROM mytable; # selects from db1.mytable
mysql> USE db2;
mysql> SELECT count(*) FROM mytable; # selects from db2.mytable
Making a particular database current by means of the USE statement does not preclude you from accessing tables in other databases. The example below accesses the author table from the db1 database and the editor table from the db2 database:
mysql> USE db1;
mysql> SELECT author_name,editor_name FROM author,db2.editor
WHERE author.editor_id = db2.editor.editor_id;
The USE statement is provided for Sybase compatibility.

DESCRIBE Syntax
{DESCRIBE | DESC} tbl_name {col_name | wild}
DESCRIBE is a shortcut for SHOW COLUMNS FROM.
DESCRIBE provides information about a table’s columns. col_name may be a column name or a string containing the SQL ‘%’ and ‘_’ wild-card characters.
If the column types are di erent than you expect them to be based on a CREATE TABLE statement, note that MySQL sometimes changes column types.
This statement is provided for Oracle compatibility.
The SHOW statement provides similar information.
MySQL Transactional and Locking Commands
BEGIN/COMMIT/ROLLBACK Syntax
By default, MySQL runs in autocommit mode. This means that as soon as you execute an update, MySQL will store the update on disk.
If you are using transactions safe tables (like InnoDB, BDB, you can put MySQL into non- autocommit mode with the following command:
SET AUTOCOMMIT=0

After this you must use COMMIT to store your changes to disk or ROLLBACK if you want to ignore the changes you have made since the beginning of your transaction.
If you want to switch from AUTOCOMMIT mode for one series of statements, you can use the BEGIN or BEGIN WORK statement:
BEGIN;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summmary=@A WHERE type=1;
COMMIT;
Note that if you are using non-transaction-safe tables, the changes will be stored at once, independent of the status of the autocommit mode.
If you do a ROLLBACK when you have updated a non-transactional table you will get an error (ER_WARNING_NOT_COMPLETE_ROLLBACK) as a warning. All transactional safe tables will be
restored but any non-transactional table will not change.
If you are using BEGIN or SET AUTOCOMMIT=0, you should use the MySQL binary log for backups instead of the older update log. Transactions are stored in the binary log in one chunk, upon COMMIT, to ensure that transactions which are rolled back are not stored.
The following commands automatically end a transaction (as if you had done a COMMIT
before executing the command):
ALTER TABLE BEGIN
CREATE INDEX
DROP DATABASE DROP TABLE RENAME TABLE
TRUNCATE

LOCK TABLES/UNLOCK TABLES Syntax
LOCK TABLES tbl_name [AS alias] {READ | [READ LOCAL] | [LOW_PRIORITY] WRITE}
[, tbl_name {READ | [LOW_PRIORITY] WRITE} ...]
...
UNLOCK TABLES
LOCK TABLES locks tables for the current thread. UNLOCK TABLES releases any locks held by the current thread. All tables that are locked by the current thread are automatically unlocked when the thread issues another LOCK TABLES, or when the connection to the server is closed.
The main reasons to use LOCK TABLES are for emulating transactions or getting more speed when updating tables. This is explained in more detail later.
If a thread obtains a READ lock on a table, that thread (and all other threads) can only read from the table. If a thread obtains a WRITE lock on a table, then only the thread holding the lock can READ from or WRITE to the table. Other threads are blocked.
The di erence between READ LOCAL and READ is that READ LOCAL allows non-con icting INSERT statements to execute while the lock is held. This can’t however be used if you are going to manipulate the database files outside MySQL while you hold the lock.
When you use LOCK TABLES, you must lock all tables that you are going to use and you must use the same alias that you are going to use in your queries! If you are using a table multiple times in a query (with liases), you must get a lock for each alias!
WRITE locks normally have higher priority than READ locks, to ensure that updates are processed as soon as possible. This means that if one thread obtains a READ lock and then another thread requests a WRITE lock, subsequent READ lock requests will wait until the WRITE thread has gotten the lock and released it. You can use LOW_PRIORITY WRITE locks to allow other threads to obtain READ locks while the thread is waiting for the WRITE lock.
You should only use LOW_PRIORITY WRITE locks if you are sure that there will eventually be a time when no threads will have a READ lock.
LOCK TABLES works as follows:
1. Sort all tables to be locked in a internally defined order (from the user standpoint the order is undefined).
2. If a table is locked with a read and a write lock, put the write lock before the read lock.
3. Lock one table at a time until the thread gets all locks.

If you are using a LOW_PRIORITY_WRITE lock for a table, this means only that MySQL will wait for this particlar lock until there is no threads that wants a READ lock. When the thread has got the WRITE lock and is waiting to get the lock for the next table in the lock table list, all other threads will wait for the WRITE lock to be released. If this becomes a serious problem with your application, you should consider converting some of your tables to transactions safe tables.
Note that you should not lock any tables that you are using with INSERT DELAYED. This is because that in this case the INSERT is done by a separate thread.
Normally, you don’t have to lock tables, as all single UPDATE statements are atomic; no other thread can interfere with any other currently executing SQL statement. There are a
few cases when you would like to lock tables anyway:
• If you are going to run many operations on a bunch of tables, it’s much faster to lock the tables you are going to use. The downside is, of course, that no other thread can update a READ-locked table and no other thread can read a WRITE-locked table.
The reason some things are faster under LOCK TABLES is that MySQL will not ush the key cache for the locked tables until UNLOCK TABLES is called (normally the key cache is ushed after each SQL statement). This speeds up inserting/updateing/deletes on MyISAM tables.
• If you are using a table handler in MySQL that doesn’t support transactions, you must use LOCK TABLES if you want to ensure that no other thread comes between a SELECT and an UPDATE. The example shown below requires LOCK TABLES in order to execute safely:
mysql> LOCK TABLES trans READ, customer WRITE;
mysql> select sum(value) from trans where customer_id= some_id;
mysql> update customer set total_value=sum_from_previous_statement where customer_id=some_id;
mysql> UNLOCK TABLES;
Without LOCK TABLES, there is a chance that another thread might insert a new row in the trans table between execution of the SELECT and UPDATE statements.
By using incremental updates (UPDATE customer SET value=value+new_value) or the LAST_INSERT_ID() function, you can avoid using LOCK TABLES in many cases.
You can also solve some cases by using the user-level lock functions GET_LOCK() and RELEASE_LOCK(). These locks are saved in a hash table in the server and implemented with pthread_mutex_lock() and pthread_mutex_unlock() for high speed.
NOTE: LOCK TABLES is not transaction-safe and will automatically commit any active trans-
actions before attempting to lock the tables.

SET TRANSACTION Syntax
SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
[READ UNCOMMITTED |READ COMMITTED | REPEATABLE READ | SERIALIZABLE]
Sets the transaction isolation level for the global, whole session or the next transaction.
The default behavior is to set the isolation level for the next (not started) transaction.
If you set the GLOBAL privilege it will a ect all new created threads. You will need the PROCESS privilege to do do this.
Setting the SESSION privilege will a ect the following and all future transactions.
You can set the default isolation level for mysqld with --transaction-isolation=....

Since Version 3.23.23, MySQL has support for full-text indexing and searching. Full-text indexes in MySQL are an index of type FULLTEXT. FULLTEXT indexes can be created from VARCHAR and TEXT columns at CREATE TABLE time or added later with ALTER TABLE or CREATE INDEX. For large datasets, adding FULLTEXT index with ALTER TABLE (or CREATE INDEX) would be much faster than inserting rows into the empty table with a FULLTEXT index.
Full-text search is performed with the MATCH function.
mysql> CREATE TABLE articles (
-> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
-> title VARCHAR(200),
-> body TEXT,
-> FULLTEXT (title,body)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO articles VALUES
-> (0,’MySQL Tutorial’, ’DBMS stands for DataBase Management ...’),
-> (0,’How To Use MySQL Efficiently’, ’After you went through a ...’),
-> (0,’Optimising MySQL’,’In this tutorial we will show how to ...’),
-> (0,’1001 MySQL Trick’,’1. Never run mysqld as root. 2. Normalise ...’),
-> (0,’MySQL vs. YourSQL’, ’In the following database comparison we ...’),
-> (0,’MySQL Security’, ’When configured properly, MySQL could be ...’);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST (’database’);
+----+-------------------+---------------------------------------------+
| id | title | body
|
+----+-------------------+---------------------------------------------+
| 5 | MySQL vs. YourSQL | In the following database comparison we ... |

MySQL Technical Reference for Version 4.0.1-alpha
| 1 | MySQL Tutorial | DBMS stands for DataBase Management ... |
+----+-------------------+---------------------------------------------+
2 rows in set (0.00 sec)
The function MATCH matches a natural language query AGAINST a text collection (which is simply the set of columns covered by a FULLTEXT index). For every row in a table it returns relevance - a similarity measure between the text in that row (in the columns that are part of the collection) and the query. When it is used in a WHERE clause (see example above) the rows returned are automatically sorted with relevance decreasing. Relevance
is a non-negative oating-point number. Zero relevance means no similarity. Relevance is computed based on the number of words in the row, the number of unique words in that row, the total number of words in the collection, and the number of documents (rows) that contain a particular word.
The above is a basic example of using MATCH function. Rows are returned with relevance decreasing.
mysql> SELECT id,MATCH (title,body) AGAINST (’Tutorial’) FROM articles;
+----+-----------------------------------------+
| id | MATCH (title,body) AGAINST (’Tutorial’) |
+----+-----------------------------------------+
| 1 |
0.64840710366884 |
| 2 |
0 |
| 3 |
0.66266459031789 |
| 4 |
0 |
| 5 |
0 |
| 6 |
0 |
+----+-----------------------------------------+
5 rows in set (0.00 sec)
This example shows how to retrieve the relevances. As neither WHERE nor ORDER BY clauses are present, returned rows are not ordered.
mysql> SELECT id, body, MATCH (title,body) AGAINST (
-> ’Security implications of running MySQL as root’) AS score
-> FROM articles WHERE MATCH (title,body) AGAINST
-> (’Security implications of running MySQL as root’);
+----+-----------------------------------------------+-----------------+
| id | body
| score |
+----+-----------------------------------------------+-----------------+
| 4 | 1. Never run mysqld as root. 2. Normalise ... | 1.5055546709332 |
| 6 | When configured properly, MySQL could be ... | 1.31140957288 |
+----+-----------------------------------------------+-----------------+
2 rows in set (0.00 sec)
This is more complex example - the query returns the relevance and still sorts the rows with relevance decreasing. To achieve it one should specify MATCH twice. Note, that this will cause no additional overhead, as MySQL optimiser will notice that these two MATCH calls are identical and will call full-text search code only once.
MySQL uses a very simple parser to split text into words. A “word” is any sequence of letters, numbers, ‘’’, and ‘_’. Any “word” that is present in the stopword list or just too short (3 characters or less) is ignored.

MySQL Language Reference

Every correct word in the collection and in the query is weighted, according to its significance in the query or collection. This way, a word that is present in many documents will have lower weight (and may even have a zero weight), because it has lower semantic value in this particular collection. Otherwise, if the word is rare, it will receive a higher weight. The weights of the words are then combined to compute the relevance of the row.
Such a technique works best with large collections (in fact, it was carefully tuned this way).
For very small tables, word distribution does not re ect adequately their semantical value, and this model may sometimes produce bisarre results.
mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST (’MySQL’);
Empty set (0.00 sec)
Search for the word MySQL produces no results in the above example. Word MySQL is present in more than half of rows, and as such, is e ectively treated as a stopword (that is, with semantical value zero). It is, really, the desired behavior - a natural language query should not return every second row in 1GB table.
A word that matches half of rows in a table is less likely to locate relevant documents.
In fact, it will most likely find plenty of irrelevant documents. We all know this happens far too often when we are trying to find something on the Internet with a search engine.
It is with this reasoning that such rows have been assigned a low semantical value in this particular dataset.
Fulltext restrictions
• All parameters to the MATCH function must be columns from the same table that is part of the same fulltext index.
• The argument to AGAINST must be a constant string.
6.8.2 Fine-tuning MySQL Full-text Search
Unfortunately, full-text search has few user-tunable parameters yet, although adding some is very high on the TODO. If you have a MySQL source distribution you can more control on the MySQL sources unless you know what you are doing!
• Minimal length of word to be indexed is defined by MySQL variable ft_min_word_ length. Change it to the value you prefer, and rebuild your FULLTEXT indexes.
• The stopword list is defined in myisam/ft_static.c Modify it to your taste, recompile
MySQL and rebuild your FULLTEXT indexes.
• The 50% threshold is caused by the particular weighting scheme chosen. To disable it, change the following line in myisam/ftdefs.h:
#define GWS_IN_USE GWS_PROB to
#define GWS_IN_USE GWS_FREQ
and recompile MySQL.
This section includes a list of the fulltext features that are already implemented in the 4.0 tree.
• REPAIR TABLE with FULLTEXT indexes, ALTER TABLE with FULLTEXT indexes, and OPTIMIZE TABLE with FULLTEXT indexes are now up to 100 times faster.
• MATCH ... AGAINST is going to supports the following boolean operators:
• +word means the that word must be present in every row returned.
• -word means the that word must not be present in every row returned.
• < and > can be used to decrease and increase word weight in the query.
• ~ can be used to assign a negative weight to a noise word.
• * is a truncation operator.
Boolean search utilises a more simplistic way of calculating the relevance, that does not have a 50% threshold.
• Make all operations with FULLTEXT index faster.
• Phrase search, proximity operators
• Boolean search can work without FULLTEXT index (yes, very slow).
• Support for "always-index words". They could be any strings the user wants to treat
as words, examples are "C++", "AS/400", "TCP/IP", etc.
• Support for full-text search in MERGE tables.
• Support for multi-byte charsets.
• Make stopword list to depend of the language of the data.
• Stemming (dependent of the language of the data, of course).
• Generic user-supplyable UDF (?) preparser.
• Make the model more exible (by adding some adjustable parameters to FULLTEXT in CREATE/ALTER TABLE).