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?
**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, -?
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”. --columns=column_list, -c column_list This option takes a comma-separated list of column names as its value.
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.
Obvious but Bad 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.
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, 192.168.0.10 and want to store that 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: mysql> SELECT INET_ATON('192.168.0.10') AS ipn; +------------+ | ipn | +------------+ | 3232235530 | +------------+ mysql> SELECT INET_NTOA(3232235530) AS ipa; +--------------+ | ipa | +--------------+ | 192.168.0.10 | +--------------+ So you can store an IP address in an INT UNSIGNED (4 bytes) which is of course much more efficient and faster than a CHAR(15). INSERT INTO tbl VALUES (..., INET_ATON('192.168.0.10'), ...) In MySQL 5.0, you can even do this transformation inside a LOAD DATA INFILE command, without using temporary columns: LOAD DATA INFILE 'filename' INTO TABLE tbl ...
14.2.6 LOAD DATA INFILE Syntax. 13.2.6 LOAD DATA INFILE Syntax LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name' [REPLACE | IGNORE] INTO TABLE tbl_name [PARTITION (partition_name [, partition_name] ...)]
[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 [, col_name_or_user_var] ...)] [SET col_name={expr | DEFAULT}, [, col_name={expr | DEFAULT}] ...] 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 8.2.4.1, “Optimizing INSERT Statements”. Note LOCAL also affects error handling: