MySQL 新手問題 : ERROR 1064 (42000)

mysql

smiledog 2020-10-08 22:35:43 ‧ 14214 瀏覽

分享至

各位大神好,我查詢了相關可能原因,還是沒有解決問題
是birthday DATE NOT NULL 設置有問題嗎? 小的先謝謝大家

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT NULL,
birthday DATE NOT NULL,
email varchar(50) COLLATE utf8_unicode' at line 6

CREATE TABLE members (
id int(40) NOT NULL auto_increment,
name char(20) COLLATE utf8_unicode_ci NOT NULL,
username char(20) COLLATE utf8_unicode_ci NOT NULL,
password char(20) COLLATE utf8_unicode_ci NOT NULL,
gender char(1) COLLATE utf8_unicode_ci DEFAULT NOT NULL,
birthday DATE NOT NULL,
email varchar(50) COLLATE utf8_unicode_ci DEFAULT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO members (id, name, username.password.gender, birthday, email) VALUES
(1, 'Sam', 'member1', 'member1', 'F', '2000-01-01', 'sam@gmail.com'),
(2, 'Kevin', 'member2', 'member2', 'M', '2000-01-02', 'kevin@gmail.com'),
(3, 'Winnie', 'member3', 'member3', 'M', '2000-01-03', 'winnie@gmail.com'),
(4, 'Mark', 'member4', 'member4', 'F', '2000-01-04', 'mark@gmail.com'),
(5, 'Suzuki', 'member5', 'member5', 'F', '2000-01-05', 'suzuki@gmail.com');

看更多先前的討論...收起先前的討論...

listennn08 iT邦高手 5 級 ‧ 2020-10-08 22:48:00 檢舉

NOT NULL 不需要 DEFAULT
auto_increment 的欄位要為 primary key

smiledog iT邦新手 5 級 ‧ 2020-10-08 22:57:35 檢舉

id int(40) NOT NULL auto_increment,

關於primary key可以打在下面嗎?
ALTER TABLE `member` ADD PRIMARY KEY(`id);

listennn08 iT邦高手 5 級 ‧ 2020-10-08 23:05:13 檢舉

這樣 table 會建不起來
用這個方式 id int(40) NOT NULL auto_increment primary key
或用 rogeryao 大的方法

smiledog iT邦新手 5 級 ‧ 2020-10-08 23:40:16 檢舉

謝謝回答!

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

3 個回答

海綿寶寶

iT邦大神 1 級 ‧ 2020-10-08 22:49:48

CREATE TABLE 部份:
1.DEFAULT 後面要有你指定的「值」(預設值)
2.id 欄位是 auto_increment，必須用他建一個 KEY

修改結果如下

CREATE TABLE members (
id int(40) NOT NULL auto_increment,
name char(20) COLLATE utf8_unicode_ci NOT NULL,
username char(20) COLLATE utf8_unicode_ci NOT NULL,
password char(20) COLLATE utf8_unicode_ci NOT NULL,
gender char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT "",
birthday DATE NOT NULL,
email varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT "",
KEY `key1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

回應 1
分享
檢舉

smiledog iT邦新手 5 級 ‧ 2020-10-08 23:40:23 檢舉

謝謝回答!

登入發表回應

rogeryao

iT邦超人 7 級 ‧ 2020-10-08 22:52:02

CREATE TABLE members (
id int(40) NOT NULL auto_increment,
name char(20) COLLATE utf8_unicode_ci NOT NULL,
username char(20) COLLATE utf8_unicode_ci NOT NULL,
password char(20) COLLATE utf8_unicode_ci NOT NULL,
gender char(1) COLLATE utf8_unicode_ci NOT NULL,
birthday DATE NOT NULL,
email varchar(50) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO members (name, username, password, gender, birthday, email) VALUES
('Sam', 'member1', 'member1', 'F', '2000-01-01', 'sam@gmail.com'),
('Kevin', 'member2', 'member2', 'M', '2000-01-02', 'kevin@gmail.com'),
('Winnie', 'member3', 'member3', 'M', '2000-01-03', 'winnie@gmail.com'),
('Mark', 'member4', 'member4', 'F', '2000-01-04', 'mark@gmail.com'),
('Suzuki', 'member5', 'member5', 'F', '2000-01-05', 'suzuki@gmail.com');

Demo