我使用vbnet 访问mysql插入数据 出现了一个很奇怪的问题。
我使用string变量名作为参数去向MySQL插入数据，报了一个语法错误。
于是我将sql语句打印到控制台，再复制过来访问数据库又执行成功了。
这是vbnet字符转义问题吗？
代码如下：
```vb.net
Imports System
Imports System.Globalization
Imports System.IO
Imports System.Runtime.InteropServices
Imports System.Security.Cryptography
Imports System.Text
Imports MySqlConnector
Module Program
Public My_con As MySqlConnection
Public sql As String = "insert into counts values"
Public sqlValue As String = ""
Public M_str_sqlcon As String =
String.Format("server={0};user id={1};password={2};database=test1;pooling=true;sslmode=none", "192.168.9.192", "w_root", "Aa,,1111")
Public cache As Hashtable = New Hashtable
Sub Main()
Dim reader As StreamReader = New StreamReader("C:\Users\T0014\myStudy\test1.txt")
Dim rL As String
Dim str As String
Do
rL = reader.ReadLine
If rL Is Nothing Then
Exit Do
End If
For Each str In rL.Replace(",", "").Replace(".", "").Split(" ")
StringStaging(cache, str)
Next
Loop
GetCon()
GetSql(cache.Keys, sqlValue)
sql = sql + sqlValue + ";" ' + Chr(13)
Console.WriteLine(sql)
Insert(sql, My_con)
'sql = "insert into counts values('acquire',1),('elderly',1),('economy',1),('form',1),('civilization',1),('walk',1),('Opera',1),('Many',1),('nature',1),('world',1),('both',1),('younger',1),('sides',1),('is',2),('Intangible',1),('two',1),('efforts',1),('need',2),('often',1),('on',2),('music',1),('will',1),('time-honored',1),('languages',1),('risk',1),('foreign',1),('equally',1),('heritages',9),('hjtj',1),('generation',1),('allowing',1),('science',1),('Forbidden',1),('modern',1),('universe',1),('has',1),('even',1),('in',6),('development',1),('On',2),('history',1),('good',1),('they',2),('sports',1),('blind',1),('burden',1),('habits',1),('Faced',1),('people',5),('rendered',1),('rituals',2),('historical',2),('should',5),('many',1),('thing',1),('extinctionAccording',1),('groups',1),('physical',1),('process',1),('make',1),('rootless',1),('this',1),('Without',1),('diversity',2),('social',1),('hard',1),('City',1),('legacies',1),('decide',1),('cater',1),('traditions',1),('Safeguarding',1),('Confucius-commemorating',1),('spiritual',1),('identity',1),('(2003)',1),('Great',1),('great',1),('mainly',1),('abounds',1),('Peking',1),('ancestral',1),('UNESCO’s',1),('them',2),('connect',1),('for',1),('knowledge',1),('Wall',1),('to',12),('it',3),('threats',1),('absolutely',1),('oral',1),('challenges',2),('present',1),('about',2),('the',19),('Like',1),('practices',1),('other',1),('faith',1),('or',2),('renovate',1),('because',2),('various',2),('lots',1),('schools',4),('intangible',5),('arise',1),('have',3),('that',1),('such',2),('pass',1),('hand',2),('China',1),('me',1),('It',1),('advertisements',1),('We',1),('electronic',1),('consisting',1),('their',1),('bad',1),('festivals',1),('pathetic',1),('we',5),('performing',1),('schools?',1),('cultural',7),('Cultural',2),('newspapers',1),('be',2),('Convention',1),('latest',1),('competitionWere',1),('tangible',1),('private',5),('whether',1),('devices',1),('and',18),('bring',1),('our',3),('those',1),('traditional',1),('utmost',1),('Heritage',1),('poses',1),('preserve',2),('can',3),('With',1),('transmitting',1),('a',7),('country',1),('you',2),('crucial',1),('forms',1),('homeland',1),('comparison',1),('society',3),('without',3),('classified',1),('contribute',1),('also',1),('cope',1),('futureHowever',1),('As',2),('folklore',1),('past',1),('return',1),('modernization',1),('arts',1),('find',1),('onHowever',1),('at',1),('TV',1),('one',1),('possession',1),('greater',1),('age',1),('turn',1),('see',2),('all',1),('customs',1),('left',1),('of',14),('special',2),('as',2),('impersonal',1),('like',1),('read',1),('help',1),('',12),('away',1),('ethnic',1),('technology',1),('coin',1),('crafts',1),('extra',1),('inevitably',1),('existence',1),('road',1),('would',2),('dealing',1),('When',1),('with',5),('mounting',1),('are',3),('computers',1),('so',4)" &
'Insert(sql, My_con)
My_con.Close()
End Sub
'统计结果聚合成数据库插入值 value
Sub GetSql(ByVal keys As ICollection, ByRef sqlValue As String)
Dim k As String
For Each k In keys
sqlValue = sqlValue + ",(" + "'" + k + "'," & cache(k) & ")"
Next
Dim ch() As Char = sqlValue.ToCharArray
ch(0) = ""
sqlValue = ""
Dim c As Char
For Each c In ch
sqlValue = sqlValue + c
Next
End Sub
Sub StringStaging(ByRef cache As Hashtable, ByVal str As String)
Dim tmp As String
If cache.ContainsValue(str) Then
cache.Add(str, 1)
Else
tmp = cache.Item(str)
cache.Item(str) = tmp + 1
End If
End Sub
Public Function GetCon() As MySqlConnection
My_con = New MySqlConnection(M_str_sqlcon)
My_con.Open()
Return My_con
End Function
Public Function Insert(ByVal the_sql As String, ByVal con As MySqlConnection)
Dim cmd As MySqlCommand = New MySqlCommand(the_sql, con)
Dim res As Integer = cmd.ExecuteNonQuery
If (res = 0) Then
Console.Write("插入失败")
Else
Console.Write("插入成功")
End If
End Function
End Module
insert into counts values(`will`,1),(`On`,2),(`contribute`,1),(`languages`,1),(`spiritual`,1),(`ethnic`,1),(`in`,6),(`performing`,1),(`as`,2),(`be`,2),(`efforts`,1),(`blind`,1),(`existence`,1),(`preserve`,2),(`other`,1),(`science`,1),(`cultural`,7),(`extinctionAccording`,1),(`classified`,1),(`private`,5),(`Heritage`,1),(`generation`,1),(`them`,2),(`newspapers`,1),(`even`,1),(`Opera`,1),(`faith`,1),(`age`,1),(`schools?`,1),(`equally`,1),(`to`,12),(`need`,2),(`sports`,1),(`historical`,2),(`many`,1),(`you`,2),(`City`,1),(`away`,1),(`With`,1),(`and`,18),(`it`,3),(`UNESCO’s`,1),(`this`,1),(`China`,1),(`for`,1),(`practices`,1),(`people`,5),(`Intangible`,1),(`we`,5),(`lots`,1),(`oral`,1),(`diversity`,2),(`without`,3),(`modernization`,1),(`impersonal`,1),(`or`,2),(`left`,1),(`two`,1),(`would`,2),(`Confucius-commemorating`,1),(`Convention`,1),(`Great`,1),(`possession`,1),(`like`,1),(`knowledge`,1),(`utmost`,1),(`often`,1),(`see`,2),(`festivals`,1),(`Like`,1),(`can`,3),(`onHowever`,1),(`consisting`,1),(`modern`,1),(`cater`,1),(`dealing`,1),(`foreign`,1),(`music`,1),(`folklore`,1),(`renovate`,1),(`Wall`,1),(`me`,1),(`find`,1),(`allowing`,1),(`help`,1),(`threats`,1),(`coin`,1),(`hard`,1),(`sides`,1),(`those`,1),(`pass`,1),(`so`,4),(`mounting`,1),(`society`,3),(`Without`,1),(`futureHowever`,1),(`extra`,1),(`arts`,1),(`crafts`,1),(`walk`,1),(`should`,5),(`the`,19),(`economy`,1),(`present`,1),(`is`,2),(`customs`,1),(`form`,1),(`habits`,1),(`a`,7),(`It`,1),(`rituals`,2),(`their`,1),(`great`,1),(`universe`,1),(`inevitably`,1),(`social`,1),(`forms`,1),(`civilization`,1),(`Cultural`,2),(`they`,2),(`bad`,1),(`of`,14),(`TV`,1),(`bring`,1),(`pathetic`,1),(``,12),(`history`,1),(`special`,2),(`devices`,1),(`heritages`,9),(`burden`,1),(`mainly`,1),(`our`,3),(`about`,2),(`As`,2),(`world`,1),(`whether`,1),(`younger`,1),(`electronic`,1),(`When`,1),(`past`,1),(`Peking`,1),(`Safeguarding`,1),(`such`,2),(`road`,1),(`has`,1),(`good`,1),(`on`,2),(`We`,1),(`identity`,1),(`groups`,1),(`one`,1),(`advertisements`,1),(`challenges`,2),(`acquire`,1),(`Many`,1),(`absolutely`,1),(`greater`,1),(`poses`,1),(`hjtj`,1),(`decide`,1),(`country`,1),(`traditions`,1),(`legacies`,1),(`various`,2),(`time-honored`,1),(`at`,1),(`Faced`,1),(`read`,1),(`elderly`,1),(`with`,5),(`traditional`,1),(`also`,1),(`all`,1),(`arise`,1),(`transmitting`,1),(`Forbidden`,1),(`turn`,1),(`homeland`,1),(`rendered`,1),(`abounds`,1),(`(2003)`,1),(`cope`,1),(`tangible`,1),(`nature`,1),(`physical`,1),(`have`,3),(`rootless`,1),(`crucial`,1),(`are`,3),(`latest`,1),(`technology`,1),(`return`,1),(`comparison`,1),(`hand`,2),(`process`,1),(`ancestral`,1),(`thing`,1),(`because`,2),(`competitionWere`,1),(`schools`,4),(`both`,1),(`that`,1),(`make`,1),(`computers`,1),(`development`,1),(`risk`,1),(`intangible`,5),(`connect`,1);
Unhandled exception. MySqlConnector.MySqlException (0x80004005): 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 '' at line 1
at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in /_/src/MySqlConnector/Core/ResultSet.cs:line 43
at MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 130
at MySqlConnector.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 468
at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList`1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 56
at MySqlConnector.MySqlCommand.ExecuteNonQueryAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 296
at MySqlConnector.MySqlCommand.ExecuteNonQuery() in /_/src/MySqlConnector/MySqlCommand.cs:line 107
at ConsoleApp5.Program.Insert(String the_sql, MySqlConnection con) in C:\Users\T0014\source\repos\ConsoleApp5\Program.vb:line 80
at ConsoleApp5.Program.Main() in C:\Users\T0014\source\repos\ConsoleApp5\Program.vb:line 37
C:\Users\T0014\source\repos\ConsoleApp5\bin\Debug\net6.0\ConsoleApp5.exe (进程 12296)已退出，代码为 -532462766。
按任意键关闭此窗口. . .
```
