R Read MySQL Database

There are several interfaces for relational database management systems (DBMS) in R, including Microsoft SQL Server, Microsoft
Access, MySQL, Oracle, PostgreSQL, DB2, Sybase, Teradata, and SQLite. Some of these packages pass through the original
Database drivers to provide access, while others are accessed through ODBC or JDBC. Use R to access the memory
Data stored in an external database is an effective means of analyzing large data sets, and can play the advantages of both SQL and R.
(1) R connecting Databases via ODBC
Accessing a database through the RODBC package in R is perhaps the most popular way to allow R to connect to any
Having an ODBC-driven database is virtually all the databases on the market.
The first step is to install and configure the appropriate ODBC drivers for your system and database type--they are not part of R
Score of If your machine does not have the necessary drivers installed, search the Internet to find it.
After installing and configuring the driver for the selected database, install the RODBC package. You can use the command
Install.packages ("RODBC") to install it.
Main functions in the RODBC package:
Odbcconnect (dsn,uid= "", pwd= "") Establish a connection to the ODBC database
SqlFetch (channel,sqltable) reads a table from an ODBC database into a data frame
SQLQuery (channel,query) submits a query to the ODBC database and returns the results
Sqlsave (Channel,mydf,tablename=sqtable,append=false) writes or updates a data frame (append=true) to a table in an ODBC database
Sqldrop (channel,sqtable) Deleting a table in an ODBC database
Close (channel) Closed connection
The RODBC package allows two-way communication between R and an ODBC-connected SQL database. This means that you can not only
To read the data in the database to r, and you can also use R to modify the contents of the database.

Library (RODBC)
MyConn <-odbcconnect ("Mysqldata", uid= "root", pwd= "admin")
Crimedat <-SqlFetch (myconn,crime)
Pundat <-sqlquery (myconn, "select * From punishment")
Close (myconn)

First, the RODBC package is loaded, and a registered data source name (mysqldata) and user name (root) are
and password (Admin) opens an ODBC database connection. The connection string is passed to SQLFetch, which copies the crime table into the R data frame Crimedat. We then executed the SQL statement select for the punishment table and insured the results
Save to the data frame pundat. Finally, we closed the connection.
The function sqlquery () is very powerful because it can insert any valid SQL statement. This flexibility gives you
The ability to specify variables, take subsets of data, create new variables, and re-encode and rename existing variables.

(2) R read MySQL data garbled problem solved

When configuring ODBC, in details select the Chinese character GBK format, see picture

R Read MySQL Database