MySQL Use tips

Convert IP addresses and numbers with MySQL built-in functions

Take advantage of two built-in functions

Inet_aton: Converts an IP address to a digital type

Inet_ntoa: Converts a numeric type to an IP address

Take full advantage of MySQL's built-in format function

Especially when dealing with character formatting, such as converting 12345 to 12,345, just use: Format (12345,0), if the format (12345,2) is displayed 12,345.00 ...

Using MySQL's built-in functions to handle timestamp problems

Eg:select From_unixtime (Unix_timestamp (), '%Y%d%m%h:%i:%s%x ');

Result: 3rd August 03:35:48 2004

Using Mysql_convert_table_format to convert table types

Need DBI and DBD MySQL related module support to use, examples:

Mysql_convert_table_format--user=root--password= ' xx '--type=myisam test YEJR

Modify the name of a field in the MySQL table

ALTER TABLE tb_name change Old_col new_col definition ...

Using temporary variables

Select @var1: =a1+a2 as A_sum, @var2: =b1+b2 as B_sum, @var1 + @var2 as total_sum from test_table xxx;

Storing IP addresses with type int

The original mistake thought must use bigint to be enough, later discovered that uses the int unsigned type is sufficient. :)

Use the IF function to quickly modify the value of an enum field

An example:

Update rule Set enable = if (' 0 ' = enable, ' 1 ', ' 0 ') where xxx;

Enable type: Enum (' 0 ', ' 1 ') not null default ' 0 '

Transaction cannot be nested

Avoid the number of connections caused by long sleep connections exceeding the problem

Set global variables wait_timeout and interactive_timeout for smaller values, such as

(s) enables each sleep connection to be automatically disconnected after 10s if no query has been made.

Set the MySQL client prompt (prompt)

Export mysql_ps1= "(\\u:\\h:) \\d>"

When you log on with Mysql-hlocalhost-uroot-pxx db_name, the prompt becomes:

(root:localhost:) db_name>

Easy to use:), always remind you on which server, try to avoid the occurrence of misoperation

Please check the MySQL manual for more details

Organize MyISAM fragments

1.) Run the OPTIMIZE TABLE command on a regular basis

2.) Use mysqldump data, and then import back again, this is obviously the first method is more convenient for large tables

Organize InnoDB fragments

1.) run null command, ALTER TABLE XXX Engine=innodb;

2.) Mysqldump method of Ibid.

MySQL Select a full table scan instead of an index if you think that the number of records retrieved exceeds 30% of the total record number

After the MySQL 5.0.3, the space after the varchar field is no longer deleted

When you upgrade to 4.1.1 or higher, it's hard to downgrade back to 4.0 or 4.1, because InnoDB uses more than one table space.

After MySQL 4.1, MySQL interpreted the length of the String type field as character length rather than byte length

When MySQL 4.1=>5.0, add a new startup option Innodb_table_locks, which causes the LOCK table to also request InnoDB

Table Locks. This option is turned on by default, but may cause deadlocks in autocommit=1 and lock TABLES applications

5.0.3 begins with precise math when calculating decimal values and rounding exact values, and decimal is stored in a more efficient format

Starting from 5.0.12, natural connection and use using

connections, including the derivative forms of external connections, are handled in accordance with the sql:2003 standard; This change has resulted in reduced natural connections and use using

Connection and will also display these fields in a more reasonable order, and the precedence of the comma comparison character is the same as in the join, left join

Previously, a lock waiting for a timeout would cause InnoDB to roll back all the current transactions, starting with 5.0.13 and rolling back only the most recent SQL statement

The TEXT field index order for the end of the InnoDB and MyISAM table has changed. Therefore, you need to run the CHECK table statement to repair the datasheet, and if there is an error, run the

"OPTIMIZE table" or "REPAIR table" statement repair, or even dump (with mysqldump)

The DECIMAL field created in the version MyISAM and InnoDB tables between MySQL 5.0.3 to 5.0.5 will crash after upgrading to 5.0.6.