CODEWITHSUNDEEP
mysql
Daniel Ashfall Zhou
Daniel Ashfall Zhou

Reputation: 167

MySQL tables are not aligned right

I have a question which relates to MySQL. The problem can be seen in these two images:

https://i.sstatic.net/CLO4l.jpg https://i.sstatic.net/CLO4l.jpg#1

Does anyone know why MySQL is doing this? It should show up as a nice and neat table, not this bundle of gibberish. Thanks in advance! :D

Upvotes: 1

Views: 4456

Answers (3)

varadharajan
varadharajan

Reputation: 1

You can try supplying a line separator character in the end.

mysql> LOAD DATA LOCAL INFILE *file_path* INTO *table_name* LINES TERMINATED BY '\r\n';

Separator character may vary for editors. In Windows, most editors use '\r\n'.

Upvotes: 0

Wolfgang Fahl
Wolfgang Fahl

Reputation: 15776

I assume you created your table somewhat like this:

create table automobile (make char(10),model char(10),year int, color char(10), style char(50), MSRP int);
insert into automobile values ('Ford','Mustang',2006,'Blue','Convertible',27000);
insert into automobile values ('Toyota','Prius',2005,'Silver','Hybrid',22000);
insert into automobile values ('Toyota','Camry',2006,'Blue','Sedan',26000);
insert into automobile values ('Dodge','1500',2005,'Green','Pickup',26000);

so a

 describe automobile

will show you your columns as:

+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| make  | char(10) | YES  |     | NULL    |       |
| model | char(10) | YES  |     | NULL    |       |
| year  | int(11)  | YES  |     | NULL    |       |
| color | char(10) | YES  |     | NULL    |       |
| style | char(50) | YES  |     | NULL    |       |
| MSRP  | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+

as long as your columns in total are smaller than your terminal's width you should see the expected result:

mysql> select * from automobile;
+--------+---------+------+--------+-------------+-------+
| make   | model   | year | color  | style       | MSRP  |
+--------+---------+------+--------+-------------+-------+
| Ford   | Mustang | 2006 | Blue   | Convertible | 27000 |
| Toyota | Prius   | 2005 | Silver | Hybrid      | 22000 |
| Toyota | Camry   | 2006 | Blue   | Sedan       | 26000 |
| Dodge  | 1500    | 2005 | Green  | Pickup      | 28000 |
+--------+---------+------+--------+-------------+-------+

if you'd like the result smaller then pick the columns you'd like to see e.g.

 select make,model from automobile

mysql> select make,model from automobile;
+--------+---------+
| make   | model   |
+--------+---------+
| Ford   | Mustang |
| Toyota | Prius   |
| Toyota | Camry   |
| Dodge  | 1500    |
+--------+---------+

to make the content of a column smaller you may use the left string function

select left(make,4) as make, left(model,5) as model,left(style,5) as style from automobile;
+------+-------+-------+
| make | model | style |
+------+-------+-------+
| Ford | Musta | Conve |
| Toyo | Prius | Hybri |
| Toyo | Camry | Sedan |
| Dodg | 1500  | Picku |
+------+-------+-------+

Upvotes: 0

egrunin
egrunin

Reputation: 25073

First, to show that there's nothing really wrong, try this query:

SELECT firstname FROM contact_info

That should look good. Now try this:

SELECT firstname, lastname FROM contact_info

That's how you pick individual columns.

Really you want to capture output to a file, this page shows you how: The MySQL Command-Line Tool

Then you can learn to use other programs to format it nicely.

Upvotes: 1

Related Questions