How to use MySQL built-in functions to convert IP addresses and related numbers

The following articles mainly introduce how to use MySQL built-in functions to convert IP addresses and related numbers. In actual operations, when processing character formats, such as converting 54789, we only need to use: format (12345, 0). The following describes the specific content.

Use MySQL built-in functions to convert IP addresses and numbers

Using two built-in functions

Inet_aton: converts an IP address to a number.

Inet_ntoa: convert a numeric type to an IP address

Make full use of the mysql built-in format Function

Especially when processing character formats, for example, to convert 12345 to 12,345, you only need to use: format (12345, 0), if you use format (12345, 2) 12,345.00 is displayed...

Use MySQL built-in functions to solve timestamp Problems

 
 

  
  
eg : select FROM_UnixTIME(UNIX_TIMESTAMP(),'%Y %D %M %h:%i:%s %x'); 
 
 

Results: 2004 3rd August 03:35:48 2004

Use mysql_convert_table_format to convert the table Type

DBI and DBD mysql-related modules are required for use. Example:

 
 

  
  
mysql_convert_table_format --user=root --password='xx' --type=myisam test yejr 
 
 

Modify the field name in the mysql table

 
 

  
  
alter table tb_name change old_col new_col definition... 
 
 

Use temporary variables

 
 

  
  
select @var1:=a1+a2 as a_sum,@var2:=b1+b2 as b_sum,@var1+@var2 as total_sum from test_table xxx; 
 
 

Store IP addresses of the int type

The original error was deemed to be sufficient only when the bigint type was used. later it was found that the int unsigned type was sufficient. :)

Use the IF function to quickly modify the ENUM Field Value

Example:

 
 

  
  
update rule set enable = if('0' = enable,'1','0') where xxx; 
 
 

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

Transactions cannot be nested

Avoid excessive connections caused by long sleep connections

Set the global variables wait_timeout and interactive_timeout to smaller values, for example

10 (s), it will automatically disconnect each sleep connection after 10 s if it has not been queried.

Set the mysql built-in function client prompt (prompt)

 
 

  
  
export MYSQL_PS1="(\u:\h:)\d> " 
 
 

After you log on with mysql-hlocalhost-uroot-pxx db_name, the prompt is changed:

 
 

  
  
(root:localhost:)db_name> 
 
 

Easy to use :), always remind you on which server to avoid misoperation

For more details, see the MySQL manual.

Sort out MyISAM fragments

1) Periodically run the optimize table command.

2) use mysqldump to output data and re-import it back. This is obviously easier for large tables to use the first method.

Organize Innodb fragments

1) run the NULL command, alter table xxx engine = Innodb;

2.) Same as mysqldump

If MySQL considers that the number of retrieved records exceeds 30% of the total number of records, select full table scan instead of Index

After MySQL 5.0.3, spaces after the VARCHAR field will not be deleted.

After upgrading to 4.1.1 or higher, it is difficult to downgrade back to 4.0 or 4.1, because InnoDB uses multiple tablespaces.

After MySQL 4.1, MySQL defines the length of a string field as a character length rather than a byte length.

When MySQL 4.1 => 5.0, a new startup option innodb_table_locks is added, which causes InnoDB to be requested during table lock.

Table lock. This option is enabled by default, but it may cause deadlocks in AUTOCOMMIT = 1 and lock tables applications.

Starting from 5.0.3, precise mathematics is used when DECIMAL values and precise rounding values are calculated. DECIMAL is stored in a more effective format.

Start from 5.0.12 and use USING

All connections, including derivative forms of external connections, are processed according to the SQL: 2003 standard; this change causes less natural connections and the use of USING

The number of result fields generated by the JOIN, and these fields will be displayed in a more reasonable order. The priority of the comma comparison operator is the same as that in JOIN and left join.

In the past, the lock waiting for timeout would cause InnoDB to roll back all the current transactions. From 5.0.13 onwards, only the most recent SQL statements will be rolled back.

The index order of TEXT fields ending with spaces in InnoDB and MyISAM tables has changed. Therefore, you must run the "check table" statement to repair the data TABLE. If an error occurs, run

Fix the "optimize table" or "repair table" statement, or even redump (use mysqldump)

After the DECIMAL field created in MyISAM and InnoDB tables between MySQL 5.0.3 and 5.0.5 is upgraded to 5.0.6, a crash occurs.

The above content is an introduction to using MySQL built-in functions to convert IP addresses and numbers. I hope you will get some benefits.