background preloader

MySQL-Skybox Tools

Facebook Twitter

Géolocalisation. How does one load IP addresses into Mysql Table from a CSV file? Import csv file into MySQL table. How to import a CSV file into MySQL database?

Import csv file into MySQL table

**Q. How can I import CSV files into MySQL tables? A. First thing, is to have a clean CSV file, one that does not have ^M chars, usually inserted by Windows editors. Then be sure to have the same structure on the CSV file than the one you have in the MySQL table. Let's say you have a file called adress.csv with this structure Name,Last Name,Address John,Wayne,Fifth Avenue Marlon,Brando,Hollywood Marilyn,Monroe,Brentwood Now that you have a table with the same structure (you can use PHPMyAdmin to create one in a MySQL database), you can import the data from the csv file into the table. mysqlimport --ignore-lines=1 --fields-terminated-by=, --verbose --local -u [user] -p [database] /path/to/address.csv --ignore-lines will ignore the number of specified lines, in this case we are omiting the first one as it is the header of the table --fields-terminated-by Will tell the command what character is the delimiter one, a comma in this case.

5.5.5 mysqlimport — A Data Import Program. --help, -?

5.5.5 mysqlimport — A Data Import Program

Display a help message and exit. --bind-address=ip_address On a computer having multiple network interfaces, use this option to select which interface to use for connecting to the MySQL server. --character-sets-dir=dir_name The directory where character sets are installed. See Section 11.5, “Character Set Configuration”. Efficient Geo IP location in MySQL database.

Efficient Geo IP location in MySQL database Inspired by Jeremy Cole and Andy Skelton articles about efficient MySQL GeoIP implementation.

Efficient Geo IP location in MySQL database

Obvious but Bad query: SELECT `code`, `country` FROM `geoip_countries` WHERE INET_ATON('') BETWEEN `ip_from` AND `ip_to` As rule MySQL cannot use index for such request. Good query: IP Address To Numeric, Numeric to IP Address, INET_ATON, INET_NTOA. If you are about to save an IP address into a MySql table of type char or varchar, then it is preferable to make use of the SQL function INET_ATON and save it in integer format instead of char or varchar.

IP Address To Numeric, Numeric to IP Address, INET_ATON, INET_NTOA

Why to convert from IP Address to Numeric (INET_ATON) ? Well, this will let you save the value into integer instead of character, and when later, you want to search your saved IP addresses, then you can assume that searching on an integer field is pretty much faster than the string i.e. char, varchar etc. Even it may help you in creating integer indexes instead of string, for the searching purpose.

How to convert from IP Address To Numeric (INET_ATON) ? While saving the IP address into numeric or integer field, you can simply use the function as follows: Storing an IP address in a database table. Say you have an IP address, and want to store that in a database table.

Storing an IP address in a database table

You could of course store it in a CHAR(15) and that is in fact what many people do. But you probably want to search on this field and therefore want it indexed also. So can we do better than using a 15 byte character field? We sure can. MySQL has two built-in functions: INET_ATON() and INET_NTOA(). Let’s put it to the test: 14.2.6 LOAD DATA INFILE Syntax. 14.2.6 LOAD DATA INFILE Syntax LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name' [REPLACE | IGNORE] INTO TABLE tbl_name [PARTITION (partition_name,...)]

14.2.6 LOAD DATA INFILE Syntax

[CHARACTER SET charset_name] [{FIELDS | COLUMNS} [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char'] ] [LINES [STARTING BY 'string'] [TERMINATED BY 'string'] ] [IGNORE number {LINES | ROWS}] [(col_name_or_user_var,...)] [SET col_name = expr,...] The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. LOAD DATA INFILE is the complement of SELECT ...

You can also load data files by using the mysqlimport utility; it operates by sending a LOAD DATA INFILE statement to the server. For more information about the efficiency of INSERT versus LOAD DATA INFILE and speeding up LOAD DATA INFILE, see Section, “Optimizing INSERT Statements”. The file name must be given as a literal string. Note The LOCAL keyword affects where the file is expected to be found: