MySQL | Linux Ask!

Prepare statement in MySQL

Jan 302010

Prepare statement in MySQL

Answer:

MySQL 5.1 provides support for server-side prepared statements, allow you to code more efficient database driven program.

Example:

mysql> PREPARE stmt_password FROM 'SELECT password(?)';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> SET @a = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt_password USING @a;
+-------------------------------------------+
| password(?)                               |
+-------------------------------------------+
| *E6CC90B878B948C35E92B003C792C46C58C4AF40 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> SET @a = 2;
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt_password USING @a;
+-------------------------------------------+
| password(?)                               |
+-------------------------------------------+
| *12033B78389744F3F39AC4CE4CCFCAD6960D8EA0 |
+-------------------------------------------+
1 row in set (0.00 sec)

As you can see above, once you have prepared the statement in server, you can reuse the statement by injecting a new value. It is faster since MySQL only need to one time SQL statement parsing, rather than parse your SQL statement every time and execute.

Create temporary table in MySQL

MySQL No Responses »

Jan 272010

Create temporary table in MySQL

Answer:

Temporary table is very useful when you need some tables for temporary storage, and they will be automatically removed when you disconnect from the MySQL server.

Steps:

1. Create a temporary table

mysql> CREATE TEMPORARY TABLE test (id int);

2. Insert some values

mysql> INSERT INTO test VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test VALUES (2);
Query OK, 1 row affected (0.00 sec)

3. Query the table

mysql> SELECT * FROM test

+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

4. Close the connection and the table will be automatically removed.

How to connect MySQL without using password

MySQL No Responses »

Jan 272010

How to connect MySQL without using password

Answer:

1. Create a .my.cnf in your home folder

# vi ~/.my.cnf

2. Enter your MySQL login information and save the file.

[client]
user=john
password=john_password

3. Try typing mysql in shell and you should able to login automatically.

How to rename database in MySQL

MySQL No Responses »

Jan 272010

How to rename database in MySQL

Answer:

If you are using MyISAM storage engine, the easiest way is to take down the MySQL server and rename the database folder name.

Else, you can use the mysqldump tool, steps as following:

1. Export the database using mysqldump tool

# mysqldump -u root -p old_db > old_db.sql

2. Create a new database

# mysql -u root -p -e 'create database new_db'

3. Finally import into the new database

# cat old_db.sql |mysql -u root -p new_db

Disable DNS hostname lookup in MySQL

MySQL No Responses »

Jan 262010

Disable DNS hostname lookup in MySQL

Answer:

If you never use MySQL authentication by hostname, it is better to disable the DNS hostname lookup for better performance. Especially if you are always connecting from localhost, or authenticate remote host by IP address)

To disable this feature, edit the MySQL configurations, e.g. /etc/my.cnf

[mysqld]
...
...
skip-name-resolve

Add the "skip-name-resolve" will do the tricks for you.

Don't forget to restart MySQL to take effect.

# /sbin/service mysqld restart

Older Entries Newer Entries