mysql
Perl mysqlimport Equivalent
Submitted by badzilla on Sun, 25/04/2010 - 10:34MySQL has a command line utility mysqlimport which imports CSV files into an MySQL database. In fact, mysqimport is a command line wrapper for LOAD DATA INFILE. Whilst constructing a price comparison website, I came across the problem of uploading merchant product catalogues, which could be 1,000,000 records+, into my MySQL database. The catalogues were provided in CSV format. Great, I thought, I'll use mysqlimport. My joy however was short lived - my host does not allow the use of the LOAD command from the filesystem. No problem, I thought, I'll use PHP.
MySQL Find Column Name in any Table in Entire Database
Submitted by badzilla on Mon, 08/02/2010 - 19:58It frequently happens, particularly when I am using Drupal, I need to find a name of a column that could be in any table in a database. There is a quick and easy way of finding this, and the syntax is:
SELECT table_name, column_name from information_schema.columns WHERE column_name LIKE '%Name%';This need cropped up the other day when I was searching Drpual for all tables that contained a column with the name description. I used the command line interface, and my output is captured below.
/tmp> mysql -u root -p
Enter password: openSUSE 11.1 to 11.2 Upgrade: Getting MySQL 5.1 to Work
Submitted by badzilla on Fri, 04/12/2009 - 20:59A new version of MySQL, version 5.1, is part of the openSUSE 11.2 upgrade. Once you have got the basic openSUSE 11.2 upgrade working, you will discover to your horror that MySQL is not. There are a couple of changes made to MySQL at this release.
Firstly, The MySQL socket is moved to /var/run/mysql. So you will need to edit your /etc/my.cnf file. Open this file and change all occurrences of the socket path to reflect this new path. So, for example:
socket = /var/run/mysql/mysql.sockThis will occur more than once so make sure you change them all!
MySQL Subqueries - a Useful Example
Submitted by badzilla on Sat, 07/11/2009 - 10:44I can remember when MySQL hit the streets back in 1995 - it was quite a raw product but still extremely useful for some development I was engaged in on a Linux Softlanding platformed box. While MySQL grew to be more and more popular, Linux SLS withered and died due to its bugs, but was still responsible for spawning the Debian distro amongst others.
MySQL: Set UTF-8 Throughout
Submitted by badzilla on Sat, 07/11/2009 - 10:40The MySQL manual states By default, MySQL uses the latin1 (cp1252 West European) character set and the latin1_swedish_ci collation that sorts according to Swedish/Finnish rules. These defaults are suitable for the United States and most of Western Europe. That's certainly true but only half the story. If you have ever developed PHP or Perl applications, sooner or later you are bound to notice inexplicable hieroglyphics in your website output. Closer inspection of these characters and you'll probably realise it is an apostrophe or an accented letter being rendered incorrectly.
