field looks weird using select *
I just started using mariadb. I created two tables so far : the first one - no issues. But, when I try to select * from the 2nd table, the primary key field looks incomplete:
+--------+--------+---------------+----------------+
| teamid | league | teamname1 | teamname2 |
+--------+--------+---------------+----------------+
| A | Anaheim | Angels | |
| ARI | N | Arizona | Diamondbacks |
| N | Atlanta | Braves | |
| A | Baltimore | Orioles | |
| A | Boston | Red Sox | |
| A | Chicago | White Sox | |
| N | Chicago | Cubs |
... (look past the copy/paste formatting issues please)
The teamid column is not printed out for every teamid, and based on the '|' separator, it looks like the field has different lengths, but it's just a char(3) fixed-length. I tried defining the field as nvarchar(3), and char(3), with the same results. I thought it had to do with the file from which the data was imported, but it contains no control characters. It's a ':' separated field that looks like: ANA:A:Anaheim:Angels BAL:A:Baltimore:Orioles BOS:A:Boston:Red Sox CHA:A:Chicago:White Sox ... file imported using :
LOAD DATA INFILE '<file>' INTO TABLE Tteams FIELDS TERMINATED BY ':'; thoughts?
Answer
This is a pretty common issue. You have loaded a file that has Windows line endings (CR-LF, '\r\n'), but by default LOAD DATA uses '\n' as the line terminator. So every value in the last column has an extra '\r' character at the end.
To load your file properly use
LOAD DATA ... LINES TERMINATED BY '\r\n'
