mysql wrong select output

Question

I created table using command:

CREATE TABLE genre(
    genre_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20) NOT NULL
);

Then I filled table with data using command:

load data infile 'D:/MySQL/.../genres.txt' into table genre(name);

There is one record on every line.

Problem occured when I tried to print content of table into terminal using command:

select * from genre;

Output:

 +----------+--------------+
 | genre_id | name         |
 +----------+--------------+
 |       1 | alternative
   | 2 | blues
 |   3 | classic
 |   4 | country
   | 5 | dance
 |   6 | electro
    |7 | folk
     | | pop
     | | rap
    |0 | jazz
  | 11 | latino
   |12 | opera
     | | r&b
  | 14 | reagge
    |5 | rock
 |       16 | metal        |
   |
 +----------+--------------+

(vertical bars are printed wrong too)

Does anyone know why is output of first column printed wrong? I am mysql beginner so sorry if it is a dumb question.

File content:

alternative
blues
classic
country
dance
electro
folk
pop
rap
jazz
latino
opera
r&b
reagge
rock
metal

Looks like your input file has white-spaces. Can you try running SELECT genre_id, TRIM(name) AS name. — Riz
– Riz, Commented Mar 23, 2014 at 23:55
Someone edited output code. | characters are on wrong position too. — aron23
– aron23, Commented Mar 24, 2014 at 0:54

Gordon Linoff · Accepted Answer · 2014-03-24 01:19:50Z

1

This is too long for a comment.

The column genre_id is auto-incremented. So, when a row is inserted into the table, the column gets a value. It is as simple as that.

You appear to have rows with no genre_id. That is not possible. The column is declared as an integer and it cannot take on a NULL value. So, you would see some value for every inserted row.

The conclusion is that the rows that look like they are missing genre_id really are not separate rows at all. Instead, they are single rows that have embedded newline or carriage return characters that are messing up the output. If you are using a Unix-based system, you can use od (octal dump) to see the contents of the file. There is probably some correct line terminator that will fix the problem.

answered Mar 24, 2014 at 1:19

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql wrong select output

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related