Loading Data into Tables from Text Files.
Assume you have the following table.
CREATE TABLE loadtest ( pkey int(11) NOT NULL auto\_increment, name varchar(20), exam int, score int, timeEnter timestamp(14), PRIMARY KEY (pkey) ); And you have the following formatted text file as shown below with the unix “tail” command:
$ tail /tmp/out.txt 'name22999990',2,94 'name22999991',3,93 'name22999992',0,91 'name22999993',1,93 'name22999994',2,90 'name22999995',3,93 'name22999996',0,93 'name22999997',1,89 'name22999998',2,85 'name22999999',3,88 NOTE: loadtest contains the "pkey" and "timeEnter" fields which are not present in the "/tmp/out.
Getting a random roll of the dice:
CREATE TABLE dice ( d\_id int(11) NOT NULL auto\_increment, roll int, PRIMARY KEY (d\_id) ); insert into dice (roll) values (1); insert into dice (roll) values (2); insert into dice (roll) values (3); insert into dice (roll) values (4); insert into dice (roll) values (5); insert into dice (roll) values (6); select roll from dice order by rand() limit 1;
While working with databases – especially Mysql – most people seem to ask the same question over and over again when it comes to Primary Keys. Here’s my typical answer to the question(s) above (and more).
Do I need a primary key? No, you don’t need it, but if you haven’t got one, there’s probably something wrong with your data model. A primary key is the unique key to a row in a table.
There are actually two ways to wipe a mysql table either using delete or using truncate – which is better and why? So far my most commonly used form to wipe a table has simple been by using:
DELETE FROM tablename; which works fine for most cases, but mysql also offers a different method:
TRUNCATE tablename; Besides the fact that it seems to be faster (even on small tables with few rows) – which in itself is a reason to favour this method - it also has another nice feature, which may be desirable.
Mysql is usually pretty fast by default, but to keep performance to the max sometimes requires knowledge on how mysql works and how to write queries that does their job most efficiently. Today we ran across a simple example which illustrates that mysql’s ability to use an index depends on the way you write the query. Let’s make a simple table and add an index on the date column: create table members ( m_id integer auto_increment primary key, m_name char(20), m_since date not null ); create index ix_m_since on members (m_since);