CODEWITHSUNDEEP
mysqlsqlite
John
John

Reputation: 329

Convert from SQLite to MySQL

This table I created in a SQLite database:

CREATE TABLE [tickets] (
[id] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
[coupon_id] INTEGER  NULL,
[size] FLOAT  NULL,
[phone] VARCHAR(10)  NULL,
[date] DATE DEFAULT CURRENT_DATE NULL,
[time] TIME DEFAULT CURRENT_TIME NULL,
[product] TEXT  NULL
);

Now INSERT operation is:

INSERT INTO "tickets" VALUES(429,9,18.16,'949-893-5032','2010-11-30','17:46:39','Kids’ Kups Berry Interesting™');
INSERT INTO "tickets" VALUES(430,9,12.04,'847-188-1359','2010-11-25','10:54:00','Raspberry Collider™');
INSERT INTO "tickets" VALUES(431,9,14.1,'204-682-5560','2010-12-08','15:34:07','Celestial Cherry High™');

Now the same table I created in MySQL:

CREATE TABLE tickets (
id INTEGER  PRIMARY KEY AUTO_INCREMENT NOT NULL,
coupon_id INTEGER  NULL,
size FLOAT  NULL,
phone VARCHAR(10)  NULL,
date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NULL,
time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NULL,
product TEXT  NULL
);

INSERT operation for MySQL is:

INSERT INTO tickets VALUES(429,9,18.16,'949-893-5032','2010-11-30','17:46:39','Kids’ Kups Berry Interesting™');
INSERT INTO tickets VALUES(430,9,12.04,'847-188-1359','2010-11-25','10:54:00','Raspberry Collider™');
INSERT INTO tickets VALUES(431,9,14.1,'204-682-5560','2010-12-08','15:34:07','Celestial Cherry High™');

When i am inserting those values I got an error :-there can be only one TIMESTAMP column with current_timestamp in default of on update clause

…but I am not able to insert all those values into MySQL. Help me?

Upvotes: 1

Views: 1470

Answers (3)

joe
joe

Reputation: 51

Your MySQL Schema appears to be incorrect for what you're trying to insert.

Excerpt from this post: Should I use field 'datetime' or 'timestamp'? ...Timestamps in MySQL generally used to track changes to records, and are updated every time the record is changed. If you want to store a specific value you should use a datetime field.

Change your MySQL schema to something closer to:

...

  • phone VARCHAR(12) NULL,
  • date DATE DEFAULT CURRENT_DATE NULL,
  • time TIME DEFAULT CURRENT_TIME NULL,

...

Upvotes: 0

Thanatos
Thanatos

Reputation: 44354

At first glace, your varchar column is size 10, but you are inserting greater than length 10 data into it. Make sure your varchar column is wide enough for your data.

Upvotes: 1

Marco
Marco

Reputation: 57603

In SQLite you have two columns

[date] DATE DEFAULT CURRENT_DATE NULL,
[time] TIME DEFAULT CURRENT_TIME NULL,

while on MySQL you have only one

date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NULL,

and you're trying to insert two values on it...

You should try

INSERT INTO tickets VALUES(..., '2010-11-30 17:46:39', ...)

Upvotes: 1

Related Questions