Posts Tagged ‘auto increment’

Howto import a UTF8 textbook (book) in MySQL table / A simple step by step guide through on howto import books in MySQL

Tuesday, March 23rd, 2010

I was looking forward to import a textbook I own to MySQL in order to be able later to easily manipulate the text with MySQL queries. After some time spend on trying hard. Here is the steps I took to import the textbook:

1. First we create necessery database and set default charset to UTF8:

freebsd$ mysql -u root -p
# create database textbook in mysql and change it to your likings
mysql$ CREATE database "textbook";
mysql$ use textbook;
mysql$ SET NAMES UTF8;

2. Then we CREATE Necessery database that will use further as a table to import the textbook into:
Below we create the table “textbook” with one column “sentence”

mysql$ CREATE table textbook (sentence varchar(5000);

3. Now we import the book:

mysql$ LOAD DATA INFILE '/path/to/file/textbook.txt' INTO TABLE textbook;

4. Last it might be a good idea to add some extra numbered column to be able to track the lines of the textbook as below:

mysql$ ALTER table textbook ADD COLUMN ID INT NOT NULL auto_increment FIRST, ADD PRIMARY KEY(ID);

In my case the book was in cyrillic and after I’ve taken the above steps I didn’t have any problems with cyrillic letters in the table.
Of course the above method is a bit dump since it’s not flexible enough and doesn’t track the textbook parts or titles, however it’s still a good way to store example on how to store text data in mysql table and could help somebody further in his journey in learning MySQL and next to that serving some simple daily SQL taks.