JWorld@TW the best professional Java site in Taiwan
      註冊 | 登入 | 全文檢索 | 排行榜  

» JWorld@TW » JDBC/SQL討論區 » SQL 語法  

按列印兼容模式列印這個話題 列印話題    把這個話題寄給朋友 寄給朋友    訂閱主題
reply to topicthreaded modego to previous topicgo to next topic
本主題所含的標籤
無標籤
作者 為什麼此語法會出錯無法insert
smartdanny





發文: 598
積分: 1
於 2011-11-15 12:15 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
我遇到一問題就是我在insert table 是使用mutile-value 方式insert ,
但是發現有時候會insert 失敗,會出現底下的錯誤訊息
1
2
3
4
5
6
7
8
9
java.sql.SQLException: 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 '2011-11-03 14:17:38',1,0,0,1,140142,'Normal Access',1,'0',1,'00-09-0F-09-00-04')' at line 1
  at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2926)
  at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
  at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)
  at com.mysql.jdbc.Connection.execSQL(Connection.java:2972)
  at com.mysql.jdbc.Connection.execSQL(Connection.java:2902)
  at com.mysql.jdbc.Statement.execute(Statement.java:529)
  at syslogd.InsertTest.<init>(InsertTest.java:81)
  at syslogd.InsertTest.main(InsertTest.java:113)

如果只要把那一行的value拿掉就可以成功isnert 了,底下是那一行的value
1
(null,1997,7061,103583,80,201771,2,'get','/479/index.html?callid=2381320301095190868306123577&adid=3135&ucode=4&subucode=100019&xt=1320301095&xs=1d947bbb15ad9746e13a2e46b6e73c6a&adtitle="2011\\u65b0\\u6982\\u5ff5\\u6e38\\u620f\\u8bd5\\u73a9"',570,'2011-11-03 14:17:38',1,0,0,1,140142,'Normal Access',1,'0',1,'00-09-0F-09-00-04'),

請問該怎麼改那筆data才能讓data成功insert


reply to postreply to post
我是API 白癡!! 重新學習GOGO!!
作者 Re:為什麼此語法會出錯無法insert [Re:smartdanny]
roytsang





發文: 620
積分: 1
於 2011-11-15 12:18 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
第一個field 可為null 嗎?

reply to postreply to post
作者 Re:為什麼此語法會出錯無法insert [Re:roytsang]
pclevin





發文: 322
積分: 0
於 2011-11-15 13:01 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
你的時間型態是date 還是datetime

reply to postreply to post
我的網誌
http://pclevin.blogspot.com
作者 Re:為什麼此語法會出錯無法insert [Re:smartdanny]
smartdanny





發文: 598
積分: 1
於 2011-11-15 13:06 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
以下是table schema
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
CREATE TABLE IF NOT EXISTS `rawdata_out-2011-10-04` (
  `Rawid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `SrcIP` int(11) NOT NULL DEFAULT '0',
  `SrcPort` int(5) NOT NULL DEFAULT '0',
  `DstIP` int(11) NOT NULL DEFAULT '0',
  `DstPort` int(5) NOT NULL DEFAULT '0',
  `Hostname` int(11) NOT NULL DEFAULT '0',
  `Url_domain` int(11) NOT NULL DEFAULT '0',
  `Method` varchar(30) NOT NULL DEFAULT 'other',
  `Uri` text NOT NULL,
  `Byte` double(20,3) NOT NULL DEFAULT '0.000',
  `Date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `Device` int(11) NOT NULL DEFAULT '0',
  `User` int(11) NOT NULL DEFAULT '0',
  `Name` int(11) NOT NULL DEFAULT '0',
  `Group` int(11) NOT NULL DEFAULT '0',
  `tid` bigint(20) unsigned NOT NULL DEFAULT '0',
  `Keyword` varchar(255) NOT NULL DEFAULT '',
  `Project` int(11) NOT NULL DEFAULT '0',
  `Block` varchar(20) NOT NULL DEFAULT '0',
  `Bridge` int(2) NOT NULL DEFAULT '0',
  `Mac` char(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`Rawid`),
  KEY `tid` (`tid`),
  KEY `Bridge` (`Bridge`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;


reply to postreply to post
我是API 白癡!! 重新學習GOGO!!
作者 Re:為什麼此語法會出錯無法insert [Re:smartdanny]
pclevin





發文: 322
積分: 0
於 2011-11-15 13:48 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
可以看一下你的insert語句嗎

reply to postreply to post
我的網誌
http://pclevin.blogspot.com
作者 Re:為什麼此語法會出錯無法insert [Re:smartdanny]
smartdanny





發文: 598
積分: 1
於 2011-11-15 13:54 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
奇怪的是,如果我把整個mutil-value 到command line 底下 直接進mysql 把sql 貼上去,確可以成功insert.
如果把整個mutil-value用Statement.execute(sql)方式insert 就會出錯,
如果把那個有問題的sql 單獨用Statement.execute(sql)方式insert 又沒問題了!!


reply to postreply to post
我是API 白癡!! 重新學習GOGO!!
作者 Re:為什麼此語法會出錯無法insert [Re:pclevin]
smartdanny





發文: 598
積分: 1
於 2011-11-15 13:57 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
測試的code如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
package syslogd;
 
import cnm.ConnectionManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
 
public class InsertTest {
 
  String sql = "INSERT DELAYED INTO `rawdata_out-2011-11-03` Values" +
      "(null,4044,2180,346,80,113,3,'get','/jobbank/custjob/index.php?r=job&j=4b77492d433d465d3839416a4b353d23153774a2f61634528282828283f682d2c917j98&jobsource=n104bank&hotjob_chr=&jobact=appear_time',849,'2011-11-03 14:17:39',1,0,0,1,140118,'Job',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,1474,4565,149267,80,143664,2,'get','/nycs/Index34.html',424,'2011-11-03 14:17:42',1,0,0,1,140119,'Normal Access',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,1644,2854,35623,80,854,3,'get','/file/file_my.php',1091,'2011-11-03 14:17:33',1,0,0,1,140120,'Social',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,2158,1708,169291,80,2251,2,'get','/widgets/tweet_button.html',784,'2011-11-03 14:17:44',1,0,0,1,140121,'Blog',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,2158,1705,150838,80,25,2,'get','/plugins/like.php?href=http%3A%2F%2Fwww.facebook.com%2Fpages%2FYahoo%25E5%25A5%2587%25E6%2591%25A9%25E6%2596%25B0%25E8%2581%259E%2F109249609124014&layout=button_count&show_faces=0&width=90&height=24&locale=zh_TW',965,'2011-11-03 14:17:44',1,0,0,1,140122,'Social',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,2158,1678,23,80,8,2,'get','/',622,'2011-11-03 14:17:23',1,0,0,1,140123,'Portal',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,2119,4981,54,80,20,2,'get','/dictionary?p=%E8%A9%B3%E5%85%A8%E9%83%A8%E5%85%A7%E5%AE%B9',1555,'2011-11-03 14:17:41',1,0,0,1,140124,'Study',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,2844,7251,118,80,45,5,'get','/hifree_web/movie.html?data={\"mediaLink\":{\"today\":\"2011-11-02\",\"link\":\"7\"},\"radioLink\":\"data\\/radiolist.xml\"}',807,'2011-11-03 14:17:20',1,0,0,1,140125,'Audio/Video',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,4110,3908,465,80,170,3,'get','/search/s000.php?searchfrom=indexbar&k=%BA%CA%B1%B1%A5d&t=0',717,'2011-11-03 14:17:26',1,0,0,1,140126,'Trade',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,311,1715,92,80,26,3,'get','/tpcgod/odcf/odcf6021.jsp?doc_id=5180215530&doc_type=A73&job_no=*BFBH15',852,'2011-11-03 14:17:44',1,0,0,1,140127,'Normal Access',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,311,1705,92,80,26,3,'get','/tpcgod/inbox/inbox_button.jsp?star=1&ktype=A73&doc_id=5180215530&status=1-1-11-',861,'2011-11-03 14:17:44',1,0,0,1,140128,'Normal Access',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,311,1714,92,80,26,3,'get','/tpcgod/odcf/odcf6021.jsp?doc_id=5180550983&doc_type=A73&job_no=*BFBH15',852,'2011-11-03 14:17:44',1,0,0,1,140129,'Normal Access',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,311,1705,92,80,26,3,'get','/tpcgod/inbox/inbox_button.jsp?star=1&ktype=A73&doc_id=5180550983&status=1-1-11-',861,'2011-11-03 14:17:44',1,0,0,1,140130,'Normal Access',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,311,1712,92,80,26,3,'get','/tpcgod/odcf/odcf6021.jsp?doc_id=5180610943&doc_type=A73&job_no=*BFAD15',852,'2011-11-03 14:17:41',1,0,0,1,140131,'Normal Access',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,311,1705,92,80,26,3,'get','/tpcgod/inbox/inbox_button.jsp?star=1&ktype=A73&doc_id=5180610943&status=1-1-11-',861,'2011-11-03 14:17:41',1,0,0,1,140132,'Normal Access',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,311,1705,92,80,26,3,'get','/tpcgod/inbox/inbox_button.jsp?star=1&ktype=A73&doc_id=5180899104&status=1-1-11-',861,'2011-11-03 14:17:40',1,0,0,1,140133,'Normal Access',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,2448,3187,169291,80,2251,2,'get','/widgets/tweet_button.html',859,'2011-11-03 14:17:44',1,0,0,1,140134,'Blog',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,9005,1535,115872,80,319298,5,'get','/blog/post/25543841',984,'2011-11-03 14:17:34',1,0,0,1,140135,'Blog',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,344,3268,516,80,195,1,'post','/CLM/CLM/ASPX/CLM20420E.ASPX',3843,'2011-11-03 14:17:38',1,0,0,1,140136,'Normal Access',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,1040,2951,250,80,75,2,'get','/chimin-feng/photo?pid=0&prev=1318&fid=36',2549,'2011-11-03 14:17:41',1,0,0,1,140137,'Blog',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,42,4948,112973,80,264,2,'get','/y319/article/43255220',987,'2011-11-03 14:17:36',1,0,0,1,140138,'Normal Access',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,6540,54398,79526,80,153064,2,'get','/lining/lining2009/racket_np200a.htm',450,'2011-11-03 14:17:43',1,0,0,1,140139,'Normal Access',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,85,4147,92,80,26,3,'get','/tpcgod/odcf/odcf6020.jsp?doc_id=5180696122&doc_type=B72&job_no=BFAT24540',862,'2011-11-03 14:17:43',1,0,0,1,140140,'Normal Access',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,111625,2528,1257,80,117,2,'get','/NEWS/2004/AD/news-300210-gio1031.swf',686,'2011-11-03 14:17:43',1,0,0,1,140141,'NEWS',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,1997,7061,103583,80,201771,2,'get','/479/index.html?callid=2381320301095190868306123577&adid=3135&ucode=4&subucode=100019&xt=1320301095&xs=1d947bbb15ad9746e13a2e46b6e73c6a&adtitle=\"2011\\u65b0\\u6982\\u5ff5\\u6e38\\u620f\\u8bd5\\u73a9\"',570,'2011-11-03 14:17:38',1,0,0,1,140142,'Normal Access',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,1997,7014,16564,80,19586,2,'get','/search?q=%E5%A9%9A%E6%81%8B%E4%BA%A4%E5%8F%8B%E7%BD%91&start=0&ue=utf8&keyfrom=web.related.rq&lq=%E4%BA%A4%E5%8F%8B%E5%A9%9A%E6%81%',349,'2011-11-03 14:17:23',1,0,0,1,140143,'Normal Access',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,1474,4636,630,80,466,2,'get','/c.php?id=30029254',383,'2011-11-03 14:18:01',1,0,0,1,140144,'Spyware',1,'site_database',1,'00-09-0F-09-00-04')," +
      "(null,2444,4307,3717,80,46,2,'post','/ajax/canvas_ticker.php?__a=1',1423,'2011-11-03 14:18:02',1,0,0,1,140145,'GameSite',1,'custom_database',1,'00-09-0F-09-00-04')," +
      "(null,869,2218,2954,80,1780,3,'get','/shopping/Browse.do?op=vc&cid=23309&sid=158',2590,'2011-11-03 14:17:48',1,0,0,1,140146,'Trade',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,4044,2182,346,80,113,3,'post','/jobbank/joblist/joblist.cfm?jobsource=n104bank',2806,'2011-11-03 14:17:47',1,0,0,1,140147,'Job',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,780,4763,22970,80,30657,3,'get','/bbs/viewthread.php?tid=5626&extra=page%3D2&page=3',1072,'2011-11-03 14:17:37',1,0,0,1,140148,'Normal Access',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,332,3974,23,80,8,2,'get','/',682,'2011-11-03 14:17:46',1,0,0,1,140149,'Portal',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,2501,1152,6270,80,69700,3,'get','/AutoPic.asp?S=True&Path=/SC200611210004/Photo2/201110310002/SY20111031155837-455_xl.jpg',636,'2011-11-03 14:17:51',1,0,0,1,140150,'Normal Access',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,27783,1282,1104,80,501,5,'post','/check_ctno1.jsp',912,'2011-11-03 14:17:30',1,0,0,1,140151,'Living',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,143,3394,30,80,11,3,'get','/MP_122031.html',579,'2011-11-03 14:17:49',1,0,0,1,140152,'Normal Access',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,153031,3299,170256,80,319323,2,'get','/',378,'2011-11-03 14:17:33',1,0,0,1,140153,'Normal Access',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,938,1447,66,80,26,3,'get','/public/userPhrase1.jsp',572,'2011-11-03 14:17:35',1,0,0,1,140154,'Normal Access',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,491,3812,695,80,86,2,'get','/watch?v=KVXtFZvUEg4&feature=related',3083,'2011-11-03 14:17:47',1,0,0,1,140155,'Audio/Video',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,491,3829,356,80,88,2,'get','/_comet/generic?channel=generic-4077883-06c04aeabe33f755ee44677e92413789506e7a0d&r=94',1833,'2011-11-03 14:17:29',1,0,0,1,140156,'Social',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,1357,1947,58,80,23,3,'get','/item/show?21011242568642',1009,'2011-11-03 14:17:49',1,0,0,1,140157,'Trade',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,61634,3922,92,80,26,3,'get','/deptno_qry.jsp',454,'2011-11-03 14:17:51',1,0,0,1,140158,'Normal Access',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,61634,3920,92,80,26,3,'get','/tpcgod/odcf/odcf6010.jsp?doc_id=5180938538&doc_type=A61&job_no=*BFBF15',809,'2011-11-03 14:17:50',1,0,0,1,140159,'Normal Access',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,61634,3922,92,80,26,3,'get','/tpcgod/inbox/inbox_button.jsp?star=1&ktype=A61&doc_id=5180938538&status=2112111',818,'2011-11-03 14:17:50',1,0,0,1,140160,'Normal Access',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,311,1716,92,80,26,3,'get','/tpcgod/odcf/odcf6021.jsp?doc_id=5179764237&doc_type=A73&job_no=*BFBH15',852,'2011-11-03 14:17:49',1,0,0,1,140161,'Normal Access',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,311,1705,92,80,26,3,'get','/tpcgod/inbox/inbox_button.jsp?star=1&ktype=A73&doc_id=5179764237&status=2112111',861,'2011-11-03 14:17:49',1,0,0,1,140162,'Normal Access',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,9421,3460,1284,80,615,2,'get','/c/fp/ccmedia.ad.swf',701,'2011-11-03 14:17:48',1,0,0,1,140163,'Normal Access',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,144578,4852,626,80,246,5,'get','/hotnews/blog/53357009',1218,'2011-11-03 14:17:33',1,0,0,1,140164,'Blog',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,9308,3951,248,80,74,2,'post','/tw/friend/edit?mode=get&friend=Y9905739093&.done=http%3A%2F%2Ftw.page.bid.yahoo.com%2Ftw%2Fauction%2F1222299824&rr=1126575192',3773,'2011-11-03 14:17:11',1,0,0,1,140165,'Normal Access',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,2398,1849,30,80,11,3,'get','/lp.asp?CtNode=22278&CtUnit=10401&BaseDSD=61&mp=122031',1084,'2011-11-03 14:17:44',1,0,0,1,140166,'Normal Access',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,9193,2088,30,80,11,3,'get','/MP_122031.html',648,'2011-11-03 14:17:41',1,0,0,1,140167,'Normal Access',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,344,3278,516,80,195,1,'post','/CLM/CLM/ASPX/CLM20420T.ASPX',17474,'2011-11-03 14:17:46',1,0,0,1,140168,'Normal Access',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,1040,2960,250,80,75,2,'get','/chimin-feng/photo?pid=0&prev=1317&fid=36',2549,'2011-11-03 14:17:48',1,0,0,1,140169,'Blog',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,85,4158,92,80,26,3,'get','/default2.html',853,'2011-11-03 14:17:50',1,0,0,1,140170,'Normal Access',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,85,4148,92,80,26,3,'get','/default.jsp',801,'2011-11-03 14:17:47',1,0,0,1,140171,'Normal Access',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,85,4147,92,80,26,3,'get','/list3.jsp',799,'2011-11-03 14:17:47',1,0,0,1,140172,'Normal Access',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,85,4148,92,80,26,3,'get','/SignTree.jsp',802,'2011-11-03 14:17:47',1,0,0,1,140173,'Normal Access',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,85,4147,92,80,26,3,'get','/RoleMenuTree.jsp',806,'2011-11-03 14:17:47',1,0,0,1,140174,'Normal Access',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,85,4148,92,80,26,3,'get','/treeMenu3.jsp',803,'2011-11-03 14:17:47',1,0,0,1,140175,'Normal Access',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,85,4146,92,80,26,3,'get','/tpcgod/inbox/inbox_button.jsp?star=1&ktype=B72&doc_id=5180696122&status=1------',869,'2011-11-03 14:17:43',1,0,0,1,140176,'Normal Access',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,561,2664,3050,80,1858,3,'get','/SProductList.aspx?StoreID=1&CategoryID=196&ProductPage=3&RecordsPerPage=20',851,'2011-11-03 14:17:44',1,0,0,1,140177,'Trade',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,561,2664,3050,80,1858,3,'get','/SProductList.aspx?StoreID=1&CategoryID=196&ProductPage=2&RecordsPerPage=20',851,'2011-11-03 14:17:26',1,0,0,1,140178,'Trade',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,282,2672,92,80,26,3,'get','/',651,'2011-11-03 14:17:48',1,0,0,1,140179,'Normal Access',1,'0',1,'00-09-0F-09-00-04')," +
      "(null,1997,6986,398,80,11638,6,'get','/search?q=%CD%F8%C9%CF%D5%D2%C5%AE%D3%D1&opt-webpage=on&client=aff-360daohang&hl=zh-CN&ie=gb2312&newwindow=1',328,'2011-11-03 14:17:10',1,0,0,1,140180,'Portal',1,'0',1,'00-09-0F-09-00-04')";
  
  String sql2 = "INSERT DELAYED INTO `rawdata_out-2011-11-03` Values" +
      "(null,1997,7061,103583,80,201771,2,'get','/479/index.html?callid=2381320301095190868306123577&adid=3135&ucode=4&subucode=100019&xt=1320301095&xs=1d947bbb15ad9746e13a2e46b6e73c6a&adtitle=\"2011\\u65b0\\u6982\\u5ff5\\u6e38\\u620f\\u8bd5\\u73a9\"',570,'2011-11-03 14:17:38',1,0,0,1,140142,'Normal Access',1,'0',1,'00-09-0F-09-00-04')";
  public InsertTest() {
    Connection con = null;
    con = ConnectionManager.getConnection();
    try{
      Statement stemt = con.createStatement();
      stemt.execute(sql2);
      System.out.println("finish...");
      if(stemt!=null){
        stemt.close();
      }
    }catch(Exception e){
      e.printStackTrace();
    }finally{
      freeConnection(null, null, con);
    }
  }
 
  private void freeConnection(ResultSet rs, PreparedStatement ps, Connection con){
    try{
      if (rs != null) {
        rs.close();
        rs = null;
      }
      if (ps != null) {
        ps.close();
        ps = null;
      }
      if (con != null) {
        ConnectionManager.freeConnection(con);
        con = null;
      }
    }catch(Exception e){
      e.printStackTrace();
    }
  }
 
  public static void main(String[] args){
    InsertTest it = new InsertTest();
    System.exit(0);
  }
}

原始的insert 語法如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
INSERT DELAYED INTO `rawdata_out-2011-11-03` Values
(null,4044,2180,346,80,113,3,'get','/jobbank/custjob/index.php?r=job&j=4b77492d433d465d3839416a4b353d23153774a2f61634528282828283f682d2c917j98&jobsource=n104bank&hotjob_chr=&jobact=appear_time',849,'2011-11-03 14:17:39',1,0,0,1,140118,'Job',1,'0',1,'00-09-0F-09-00-04'),
(null,1474,4565,149267,80,143664,2,'get','/nycs/Index34.html',424,'2011-11-03 14:17:42',1,0,0,1,140119,'Normal Access',1,'0',1,'00-09-0F-09-00-04'),
(null,1644,2854,35623,80,854,3,'get','/file/file_my.php',1091,'2011-11-03 14:17:33',1,0,0,1,140120,'Social',1,'0',1,'00-09-0F-09-00-04'),
(null,2158,1708,169291,80,2251,2,'get','/widgets/tweet_button.html',784,'2011-11-03 14:17:44',1,0,0,1,140121,'Blog',1,'0',1,'00-09-0F-09-00-04'),
(null,2158,1705,150838,80,25,2,'get','/plugins/like.php?href=http%3A%2F%2Fwww.facebook.com%2Fpages%2FYahoo%25E5%25A5%2587%25E6%2591%25A9%25E6%2596%25B0%25E8%2581%259E%2F109249609124014&layout=button_count&show_faces=0&width=90&height=24&locale=zh_TW',965,'2011-11-03 14:17:44',1,0,0,1,140122,'Social',1,'0',1,'00-09-0F-09-00-04'),
(null,2158,1678,23,80,8,2,'get','/',622,'2011-11-03 14:17:23',1,0,0,1,140123,'Portal',1,'0',1,'00-09-0F-09-00-04'),
(null,2119,4981,54,80,20,2,'get','/dictionary?p=%E8%A9%B3%E5%85%A8%E9%83%A8%E5%85%A7%E5%AE%B9',1555,'2011-11-03 14:17:41',1,0,0,1,140124,'Study',1,'0',1,'00-09-0F-09-00-04'),
(null,2844,7251,118,80,45,5,'get','/hifree_web/movie.html?data={"mediaLink":{"today":"2011-11-02","link":"7"},"radioLink":"data\\/radiolist.xml"}',807,'2011-11-03 14:17:20',1,0,0,1,140125,'Audio/Video',1,'0',1,'00-09-0F-09-00-04'),
(null,4110,3908,465,80,170,3,'get','/search/s000.php?searchfrom=indexbar&k=%BA%CA%B1%B1%A5d&t=0',717,'2011-11-03 14:17:26',1,0,0,1,140126,'Trade',1,'0',1,'00-09-0F-09-00-04'),
(null,311,1715,92,80,26,3,'get','/tpcgod/odcf/odcf6021.jsp?doc_id=5180215530&doc_type=A73&job_no=*BFBH15',852,'2011-11-03 14:17:44',1,0,0,1,140127,'Normal Access',1,'0',1,'00-09-0F-09-00-04'),
(null,311,1705,92,80,26,3,'get','/tpcgod/inbox/inbox_button.jsp?star=1&ktype=A73&doc_id=5180215530&status=1-1-11-',861,'2011-11-03 14:17:44',1,0,0,1,140128,'Normal Access',1,'0',1,'00-09-0F-09-00-04'),
(null,311,1714,92,80,26,3,'get','/tpcgod/odcf/odcf6021.jsp?doc_id=5180550983&doc_type=A73&job_no=*BFBH15',852,'2011-11-03 14:17:44',1,0,0,1,140129,'Normal Access',1,'0',1,'00-09-0F-09-00-04'),
(null,311,1705,92,80,26,3,'get','/tpcgod/inbox/inbox_button.jsp?star=1&ktype=A73&doc_id=5180550983&status=1-1-11-',861,'2011-11-03 14:17:44',1,0,0,1,140130,'Normal Access',1,'0',1,'00-09-0F-09-00-04'),
(null,311,1712,92,80,26,3,'get','/tpcgod/odcf/odcf6021.jsp?doc_id=5180610943&doc_type=A73&job_no=*BFAD15',852,'2011-11-03 14:17:41',1,0,0,1,140131,'Normal Access',1,'0',1,'00-09-0F-09-00-04'),
(null,311,1705,92,80,26,3,'get','/tpcgod/inbox/inbox_button.jsp?star=1&ktype=A73&doc_id=5180610943&status=1-1-11-',861,'2011-11-03 14:17:41',1,0,0,1,140132,'Normal Access',1,'0',1,'00-09-0F-09-00-04'),
(null,311,1705,92,80,26,3,'get','/tpcgod/inbox/inbox_button.jsp?star=1&ktype=A73&doc_id=5180899104&status=1-1-11-',861,'2011-11-03 14:17:40',1,0,0,1,140133,'Normal Access',1,'0',1,'00-09-0F-09-00-04'),
(null,2448,3187,169291,80,2251,2,'get','/widgets/tweet_button.html',859,'2011-11-03 14:17:44',1,0,0,1,140134,'Blog',1,'0',1,'00-09-0F-09-00-04'),
(null,9005,1535,115872,80,319298,5,'get','/blog/post/25543841',984,'2011-11-03 14:17:34',1,0,0,1,140135,'Blog',1,'0',1,'00-09-0F-09-00-04'),
(null,344,3268,516,80,195,1,'post','/CLM/CLM/ASPX/CLM20420E.ASPX',3843,'2011-11-03 14:17:38',1,0,0,1,140136,'Normal Access',1,'0',1,'00-09-0F-09-00-04'),
(null,1040,2951,250,80,75,2,'get','/chimin-feng/photo?pid=0&prev=1318&fid=36',2549,'2011-11-03 14:17:41',1,0,0,1,140137,'Blog',1,'0',1,'00-09-0F-09-00-04'),
(null,42,4948,112973,80,264,2,'get','/y319/article/43255220',987,'2011-11-03 14:17:36',1,0,0,1,140138,'Normal Access',1,'0',1,'00-09-0F-09-00-04'),
(null,6540,54398,79526,80,153064,2,'get','/lining/lining2009/racket_np200a.htm',450,'2011-11-03 14:17:43',1,0,0,1,140139,'Normal Access',1,'0',1,'00-09-0F-09-00-04'),
(null,85,4147,92,80,26,3,'get','/tpcgod/odcf/odcf6020.jsp?doc_id=5180696122&doc_type=B72&job_no=BFAT24540',862,'2011-11-03 14:17:43',1,0,0,1,140140,'Normal Access',1,'0',1,'00-09-0F-09-00-04'),
(null,111625,2528,1257,80,117,2,'get','/NEWS/2004/AD/news-300210-gio1031.swf',686,'2011-11-03 14:17:43',1,0,0,1,140141,'NEWS',1,'0',1,'00-09-0F-09-00-04'),
(null,1997,7061,103583,80,201771,2,'get','/479/index.html?callid=2381320301095190868306123577&adid=3135&ucode=4&subucode=100019&xt=1320301095&xs=1d947bbb15ad9746e13a2e46b6e73c6a&adtitle="2011\\u65b0\\u6982\\u5ff5\\u6e38\\u620f\\u8bd5\\u73a9"',570,'2011-11-03 14:17:38',1,0,0,1,140142,'Normal Access',1,'0',1,'00-09-0F-09-00-04'),
(null,1997,7014,16564,80,19586,2,'get','/search?q=%E5%A9%9A%E6%81%8B%E4%BA%A4%E5%8F%8B%E7%BD%91&start=0&ue=utf8&keyfrom=web.related.rq&lq=%E4%BA%A4%E5%8F%8B%E5%A9%9A%E6%81%',349,'2011-11-03 14:17:23',1,0,0,1,140143,'Normal Access',1,'0',1,'00-09-0F-09-00-04'),
(null,1474,4636,630,80,466,2,'get','/c.php?id=30029254',383,'2011-11-03 14:18:01',1,0,0,1,140144,'Spyware',1,'site_database',1,'00-09-0F-09-00-04'),
(null,2444,4307,3717,80,46,2,'post','/ajax/canvas_ticker.php?__a=1',1423,'2011-11-03 14:18:02',1,0,0,1,140145,'GameSite',1,'custom_database',1,'00-09-0F-09-00-04'),
(null,869,2218,2954,80,1780,3,'get','/shopping/Browse.do?op=vc&cid=23309&sid=158',2590,'2011-11-03 14:17:48',1,0,0,1,140146,'Trade',1,'0',1,'00-09-0F-09-00-04'),
(null,4044,2182,346,80,113,3,'post','/jobbank/joblist/joblist.cfm?jobsource=n104bank',2806,'2011-11-03 14:17:47',1,0,0,1,140147,'Job',1,'0',1,'00-09-0F-09-00-04'),
(null,780,4763,22970,80,30657,3,'get','/bbs/viewthread.php?tid=5626&extra=page%3D2&page=3',1072,'2011-11-03 14:17:37',1,0,0,1,140148,'Normal Access',1,'0',1,'00-09-0F-09-00-04'),
(null,332,3974,23,80,8,2,'get','/',682,'2011-11-03 14:17:46',1,0,0,1,140149,'Portal',1,'0',1,'00-09-0F-09-00-04'),
(null,2501,1152,6270,80,69700,3,'get','/AutoPic.asp?S=True&Path=/SC200611210004/Photo2/201110310002/SY20111031155837-455_xl.jpg',636,'2011-11-03 14:17:51',1,0,0,1,140150,'Normal Access',1,'0',1,'00-09-0F-09-00-04'),
(null,27783,1282,1104,80,501,5,'post','/check_ctno1.jsp',912,'2011-11-03 14:17:30',1,0,0,1,140151,'Living',1,'0',1,'00-09-0F-09-00-04'),
(null,143,3394,30,80,11,3,'get','/MP_122031.html',579,'2011-11-03 14:17:49',1,0,0,1,140152,'Normal Access',1,'0',1,'00-09-0F-09-00-04'),
(null,153031,3299,170256,80,319323,2,'get','/',378,'2011-11-03 14:17:33',1,0,0,1,140153,'Normal Access',1,'0',1,'00-09-0F-09-00-04'),
(null,938,1447,66,80,26,3,'get','/public/userPhrase1.jsp',572,'2011-11-03 14:17:35',1,0,0,1,140154,'Normal Access',1,'0',1,'00-09-0F-09-00-04'),
(null,491,3812,695,80,86,2,'get','/watch?v=KVXtFZvUEg4&feature=related',3083,'2011-11-03 14:17:47',1,0,0,1,140155,'Audio/Video',1,'0',1,'00-09-0F-09-00-04'),
(null,491,3829,356,80,88,2,'get','/_comet/generic?channel=generic-4077883-06c04aeabe33f755ee44677e92413789506e7a0d&r=94',1833,'2011-11-03 14:17:29',1,0,0,1,140156,'Social',1,'0',1,'00-09-0F-09-00-04'),
(null,1357,1947,58,80,23,3,'get','/item/show?21011242568642',1009,'2011-11-03 14:17:49',1,0,0,1,140157,'Trade',1,'0',1,'00-09-0F-09-00-04'),
(null,61634,3922,92,80,26,3,'get','/deptno_qry.jsp',454,'2011-11-03 14:17:51',1,0,0,1,140158,'Normal Access',1,'0',1,'00-09-0F-09-00-04'),
(null,61634,3920,92,80,26,3,'get','/tpcgod/odcf/odcf6010.jsp?doc_id=5180938538&doc_type=A61&job_no=*BFBF15',809,'2011-11-03 14:17:50',1,0,0,1,140159,'Normal Access',1,'0',1,'00-09-0F-09-00-04'),
(null,61634,3922,92,80,26,3,'get','/tpcgod/inbox/inbox_button.jsp?star=1&ktype=A61&doc_id=5180938538&status=2112111',818,'2011-11-03 14:17:50',1,0,0,1,140160,'Normal Access',1,'0',1,'00-09-0F-09-00-04'),
(null,311,1716,92,80,26,3,'get','/tpcgod/odcf/odcf6021.jsp?doc_id=5179764237&doc_type=A73&job_no=*BFBH15',852,'2011-11-03 14:17:49',1,0,0,1,140161,'Normal Access',1,'0',1,'00-09-0F-09-00-04'),
(null,311,1705,92,80,26,3,'get','/tpcgod/inbox/inbox_button.jsp?star=1&ktype=A73&doc_id=5179764237&status=2112111',861,'2011-11-03 14:17:49',1,0,0,1,140162,'Normal Access',1,'0',1,'00-09-0F-09-00-04'),
(null,9421,3460,1284,80,615,2,'get','/c/fp/ccmedia.ad.swf',701,'2011-11-03 14:17:48',1,0,0,1,140163,'Normal Access',1,'0',1,'00-09-0F-09-00-04'),
(null,144578,4852,626,80,246,5,'get','/hotnews/blog/53357009',1218,'2011-11-03 14:17:33',1,0,0,1,140164,'Blog',1,'0',1,'00-09-0F-09-00-04'),
(null,9308,3951,248,80,74,2,'post','/tw/friend/edit?mode=get&friend=Y9905739093&.done=http%3A%2F%2Ftw.page.bid.yahoo.com%2Ftw%2Fauction%2F1222299824&rr=1126575192',3773,'2011-11-03 14:17:11',1,0,0,1,140165,'Normal Access',1,'0',1,'00-09-0F-09-00-04'),
(null,2398,1849,30,80,11,3,'get','/lp.asp?CtNode=22278&CtUnit=10401&BaseDSD=61&mp=122031',1084,'2011-11-03 14:17:44',1,0,0,1,140166,'Normal Access',1,'0',1,'00-09-0F-09-00-04'),
(null,9193,2088,30,80,11,3,'get','/MP_122031.html',648,'2011-11-03 14:17:41',1,0,0,1,140167,'Normal Access',1,'0',1,'00-09-0F-09-00-04'),
(null,344,3278,516,80,195,1,'post','/CLM/CLM/ASPX/CLM20420T.ASPX',17474,'2011-11-03 14:17:46',1,0,0,1,140168,'Normal Access',1,'0',1,'00-09-0F-09-00-04'),
(null,1040,2960,250,80,75,2,'get','/chimin-feng/photo?pid=0&prev=1317&fid=36',2549,'2011-11-03 14:17:48',1,0,0,1,140169,'Blog',1,'0',1,'00-09-0F-09-00-04'),
(null,85,4158,92,80,26,3,'get','/default2.html',853,'2011-11-03 14:17:50',1,0,0,1,140170,'Normal Access',1,'0',1,'00-09-0F-09-00-04'),
(null,85,4148,92,80,26,3,'get','/default.jsp',801,'2011-11-03 14:17:47',1,0,0,1,140171,'Normal Access',1,'0',1,'00-09-0F-09-00-04'),
(null,85,4147,92,80,26,3,'get','/list3.jsp',799,'2011-11-03 14:17:47',1,0,0,1,140172,'Normal Access',1,'0',1,'00-09-0F-09-00-04'),
(null,85,4148,92,80,26,3,'get','/SignTree.jsp',802,'2011-11-03 14:17:47',1,0,0,1,140173,'Normal Access',1,'0',1,'00-09-0F-09-00-04'),
(null,85,4147,92,80,26,3,'get','/RoleMenuTree.jsp',806,'2011-11-03 14:17:47',1,0,0,1,140174,'Normal Access',1,'0',1,'00-09-0F-09-00-04'),
(null,85,4148,92,80,26,3,'get','/treeMenu3.jsp',803,'2011-11-03 14:17:47',1,0,0,1,140175,'Normal Access',1,'0',1,'00-09-0F-09-00-04'),
(null,85,4146,92,80,26,3,'get','/tpcgod/inbox/inbox_button.jsp?star=1&ktype=B72&doc_id=5180696122&status=1------',869,'2011-11-03 14:17:43',1,0,0,1,140176,'Normal Access',1,'0',1,'00-09-0F-09-00-04'),
(null,561,2664,3050,80,1858,3,'get','/SProductList.aspx?StoreID=1&CategoryID=196&ProductPage=3&RecordsPerPage=20',851,'2011-11-03 14:17:44',1,0,0,1,140177,'Trade',1,'0',1,'00-09-0F-09-00-04'),
(null,561,2664,3050,80,1858,3,'get','/SProductList.aspx?StoreID=1&CategoryID=196&ProductPage=2&RecordsPerPage=20',851,'2011-11-03 14:17:26',1,0,0,1,140178,'Trade',1,'0',1,'00-09-0F-09-00-04'),
(null,282,2672,92,80,26,3,'get','/',651,'2011-11-03 14:17:48',1,0,0,1,140179,'Normal Access',1,'0',1,'00-09-0F-09-00-04'),
(null,1997,6986,398,80,11638,6,'get','/search?q=%CD%F8%C9%CF%D5%D2%C5%AE%D3%D1&opt-webpage=on&client=aff-360daohang&hl=zh-CN&ie=gb2312&newwindow=1',328,'2011-11-03 14:17:10',1,0,0,1,140180,'Portal',1,'0',1,'00-09-0F-09-00-04')


reply to postreply to post
我是API 白癡!! 重新學習GOGO!!
作者 Re:為什麼此語法會出錯無法insert [Re:smartdanny]
roytsang





發文: 620
積分: 1
於 2011-11-15 14:03 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
你的table schema 裡面每一個field 都是not null, 為什麼你的sql 有一個field 的value是null 呢?
還有寫insert sql 最好包含field name,

1
2
3
e.g.
 
insert into table1 (f1,f2....)value (v1,v2....)


roytsang edited on 2011-11-15 14:05
reply to postreply to post
作者 Re:為什麼此語法會出錯無法insert [Re:roytsang]
smartdanny





發文: 598
積分: 1
於 2011-11-15 14:37 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
試過了還是出現一樣的錯誤
1
2
3
4
5
6
7
8
9
java.sql.SQLException: 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 '2011-11-03 14:17:38',1,0,0,1,140142,'Normal Access',1,'0',1,'00-09-0F-09-00-04')' at line 1
  at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2926)
  at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
  at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)
  at com.mysql.jdbc.Connection.execSQL(Connection.java:2972)
  at com.mysql.jdbc.Connection.execSQL(Connection.java:2902)
  at com.mysql.jdbc.Statement.execute(Statement.java:529)
  at syslogd.InsertTest.<init>(InsertTest.java:149)
  at syslogd.InsertTest.main(InsertTest.java:181)


reply to postreply to post
我是API 白癡!! 重新學習GOGO!!
作者 Re:為什麼此語法會出錯無法insert [Re:smartdanny]
smartdanny





發文: 598
積分: 1
於 2011-11-15 14:57 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
我發現只要把發生錯誤的部份
1
(null,1997,7061,103583,80,201771,2,'get','/479/index.html?callid=2381320301095190868306123577&adid=3135&ucode=4&subucode=100019&xt=1320301095&xs=1d947bbb15ad9746e13a2e46b6e73c6a&adtitle="2011\\u65b0\\u6982\\u5ff5\\u6e38\\u620f\\u8bd5\\u73a9"',570,'2011-11-03 14:17:38',1,0,0,1,140142,'Normal Access',1,'0',1,'00-09-0F-09-00-04'),

此段的雙引號拿掉就可以了,如果我還是需要那雙引號那我該如果讓它insert 成功?
1
"2011\\u65b0\\u6982\\u5ff5\\u6e38\\u620f\\u8bd5\\u73a9"'


reply to postreply to post
我是API 白癡!! 重新學習GOGO!!
作者 Re:為什麼此語法會出錯無法insert [Re:smartdanny]
smartdanny





發文: 598
積分: 1
於 2011-11-15 15:30 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
smartdanny wrote:
我發現只要把發生錯誤的部份
1
(null,1997,7061,103583,80,201771,2,'get','/479/index.html?callid=2381320301095190868306123577&adid=3135&ucode=4&subucode=100019&xt=1320301095&xs=1d947bbb15ad9746e13a2e46b6e73c6a&adtitle="2011\\u65b0\\u6982\\u5ff5\\u6e38\\u620f\\u8bd5\\u73a9"',570,'2011-11-03 14:17:38',1,0,0,1,140142,'Normal Access',1,'0',1,'00-09-0F-09-00-04'),

此段的雙引號拿掉就可以了,如果我還是需要那雙引號那我該如果讓它insert 成功?
1
"2011\\u65b0\\u6982\\u5ff5\\u6e38\\u620f\\u8bd5\\u73a9"'



已經可以insert了 原來雙引號也必須要使用 跳脫字元才行


reply to postreply to post
我是API 白癡!! 重新學習GOGO!!
» JWorld@TW »  JDBC/SQL討論區 » SQL 語法

reply to topicthreaded modego to previous topicgo to next topic
  已讀文章
  新的文章
  被刪除的文章
Jump to the top of page

JWorld@TW 本站商標資訊

Powered by Powerful JuteForum® Version Jute 1.5.8