NOLOCK equivalent in MySQL

SQL Server WITH (NOLOCK) looks like this:

SELECT * FROM TABLE WITH (nolock)

To achieve the same with MySQL, we change the session isolation mode using the SET SESSION command.

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT * FROM TABLE_NAME ;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ;

This statement will work similar to WITH (NOLOCK) i.e READ UNCOMMITTED data. We can also set the isolation level for all connections globally:

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;

In addition, two system variables related to isolation also level exist in MySQL:

--global isolation level
SELECT @@global.tx_isolation; 
--session isolation level
SELECT @@tx_isolation; 

Or set the isolation level inside a transaction:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

MySQL Isolation levels documentation is here

Export table to CSV in MySQL

Using the OUTFILE option, i ran the following:

--This select is for the headings
SELECT 
'id', 'created_at', 'searches_id', 'user_id', 'msisdn', 
'frequency', 'status', 'is_queue', 'mo_id', 'deleted_at', 
'restarted_at', 'last_visited_at', 'last_checked_at' 
UNION
--This select is for the data
SELECT * INTO OUTFILE '/tmp/savedtable.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
FROM savethistable;

The Union is to allow for headings. To get a list of columns in a table do:

show columns from savethistable;

If you don’t specify a path then default path is whatever is specified as the datadir in the my.cnf configuration file.

Eg:

[dbamohsin@myserver /tmp]$ less /etc/my.cnf

################# MASTER #######################

[mysqld]

datadir=/var/site_db/master

Another way of doing this:

show variables like 'data%';

Introduction to MySQL

Decided to learn a bit more about MySQL as my team at work will soon be taking on some MySQL instances.

Installation: http://dev.mysql.com/downloads/ (MySQL 5.5)

How to Install: http://www.wikihow.com/Install-the-MySQL-Database-Server-on-Your-Windows-PC

The installation on windows is fairly painless. took around 5-10 minutes to install with simple click through windows.

Administration via a GUI?: I started using the MySQL Workbench (5.2 CE) which ships with the installation. Other tools are available including probably the most widely known which is the MAAT Kit (http://www.maatkit.org/)

Workbench home screen:

image

Workbench simple Query Editor:

image

Admin Page:

image

Command Line: the command line interface initially didn’t work for me. To resolve this i had to add the MySQL bin directory (C:\Program Files (x86)\MySQL\MySQL Server 5.5\bin) into the environment variables on my Machine.

To do this on vista Right click my Computer | Properties | Advanced System Settings [Left pane] | Environment Variables…

Connecting via CMD

mysql -u root -p -h localhost

Some other Simple commands:

show databases;

use database;

show tables;

Reference Manual: http://dev.mysql.com/doc/refman/5.5/en/index.html