mySQL adding column breaks data

Question

I have succesfully created a database in mySQL using the commandline and imported some data. It currently looks like this..

desc data;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| code     | varchar(10) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+

SELECT * FROM data;
    +----+----------+
    | id | code     |
    +----+----------+
     | 1 | 123abc
     | 2 | 234def
     | 3 | 567ghi
     | 4 | 890jkl

I would like to add a column to the table called timestamp, I am doing this with..

alter table data add timestamp VARCHAR(20);

But then my table looks like this...

desc data;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| code      | varchar(10) | YES  |     | NULL    |                |
| timestamp | varchar(20) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+

SELECT * FROM data;
    +----+----------+-----------+
    | id | code     | timestamp |
    +----+----------+-----------+
     | NULL       |
     | NULL       |
     | NULL       |
     | NULL       |

Where am I going wrong?

TIMESTAMP is a Keyword so you must put it in backticks if you want to use as fieldname. -alter table data add timestamp VARCHAR(20); - i cant show it in comment — Bernd Buffen
– Bernd Buffen, Commented Mar 10, 2016 at 20:45
There's an example here. stackoverflow.com/questions/17541312/… It's adding multiple columns, but should help. — BryanT
– BryanT, Commented Mar 10, 2016 at 21:05
Could you please supply the output of describe data? And how did you list the table's content? select * from data? MySQL formats the output different from what you showed. — PerlDuck
– PerlDuck, Commented Mar 10, 2016 at 21:09
I doubt your output does reflect select * from data. It probably is select timestamp from data, right? And you're wondering about the NULLs for timestamp? When adding columns to a table, MySQLs fills them with a default value and in your case that is NULL so all new rows have timestamp=NULL. — PerlDuck
– PerlDuck, Commented Mar 10, 2016 at 21:17

Bernd Buffen · Accepted Answer · 2016-03-10 21:11:48Z

3

here you can see the backticks

alter table `data` add `timestamp` VARCHAR(20);

SAMPLE

MariaDB []> desc data;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type                 | Null | Key | Default | Extra          |
+-------+----------------------+------+-----+---------+----------------+
| id    | int(11) unsigned     | NO   | PRI | NULL    | auto_increment |
| e     | enum('x1','x2','x3') | YES  |     | NULL    |                |
+-------+----------------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

MariaDB []> alter table `data` add `timestamp` VARCHAR(20);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB []> desc data;
+-----------+----------------------+------+-----+---------+----------------+
| Field     | Type                 | Null | Key | Default | Extra          |
+-----------+----------------------+------+-----+---------+----------------+
| id        | int(11) unsigned     | NO   | PRI | NULL    | auto_increment |
| e         | enum('x1','x2','x3') | YES  |     | NULL    |                |
| timestamp | varchar(20)          | YES  |     | NULL    |                |
+-----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

Table Data

MariaDB [who]> select * from `data`;
+----+------+-----------+
| id | e    | timestamp |
+----+------+-----------+
|  1 | x1   | NULL      |
|  2 | x2   | NULL      |
+----+------+-----------+
2 rows in set (0.00 sec)

MariaDB [who]>

edited Mar 10, 2016 at 21:11

answered Mar 10, 2016 at 20:47

Bernd Buffen

15.1k2 gold badges27 silver badges41 bronze badges

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

15 Comments

Bernd Buffen Over a year ago

did you use backticks ? copy it from my answer please and test it. i have add in my answer

Bernd Buffen Over a year ago

try : select * from data\G it look like a display error or control char in some field

Bernd Buffen Over a year ago

i think there are carriage return CR in your varchar field. so the display start again at the begin of a line and override the output. Truncate the table and insert new data

Bernd Buffen Over a year ago

so...., if you use: SELECT * FROM data; the client will display each row like this | 1 | hello | NULL | but when in the second field is a CR control char. it will display | 1 | hello an when CR outputs the cursor go back to the begin of the line and the last field overrides the output with blank and NULL . so you cant see the first output

fightstarr20 Over a year ago

Thanks Bernd, it was an issue with my original data. I was focusing on the wrong thing all along!

|

Collectives™ on Stack Overflow

mySQL adding column breaks data

1 Answer 1

15 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

15 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related