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:

+--------+--------+---------------+----------------+

teamidleagueteamname1teamname2

+--------+--------+---------------+----------------+

AAnaheimAngels
ARINArizonaDiamondbacks
NAtlantaBraves
ABaltimoreOrioles
ABostonRed Sox
AChicagoWhite Sox
NChicagoCubs

... (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'

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.