R MySQL Connection

MySQL is the most popular relational database management system. In WEB applications, MySQL is one of the best RDBMS (Relational Database Management System) application software.

If you are not familiar with MySQL, you can refer to:MySQL Tutorial

R language reading and writing MySQL files requires installing an extension package. We can enter the following command in the R console to install it:

install.packages("RMySQL", repos = "https://mirrors.ustc.edu.cn/CRAN/")

To check whether the installation is successful:

> any(grepl("RMySQL",installed.packages()))
[1] TRUE

MySQL is currently acquired by Oracle, so many people use its fork MariaDB. MariaDB is open source under GNU GPL. The development of MariaDB is led by some of the original developers of MySQL, so the syntax and operations are similar:

install.packages("RMariaDB", repos = "https://mirrors.ustc.edu.cn/CRAN/")

Create the data table example in the test database. The table structure and data code are as follows:

Example

-- -- Table structure for `example` -- CREATE TABLE `example` ( `id` int(11) NOT NULL, `name` char(20) NOT NULL, `url` varchar(255) NOT NULL, `likes` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- Dumping data for table `example` -- INSERT INTO `example` (`id`, `name`, `url`, `likes`) VALUES (1, 'Google', 'www.google.com', 111), (2, 'Example', 'www.example.com', 222), (3, 'Taobao', 'www.taobao.com', 333);

Next we can use the RMySQL package to read the data:

Example

library(RMySQL)

# dbname is the database name, please fill in the parameters here according to your actual situation
mysqlconnection = dbConnect(MySQL(), user = 'root', password = '', dbname = 'test',host = 'localhost')

# View data
dbListTables(mysqlconnection)

Next we can use dbSendQuery to read the database table, and the result set is obtained through the fetch() function:

Example

library(RMySQL)
# Query the sites table. CRUD operations can be implemented through the SQL statement of the second parameter
result = dbSendQuery(mysqlconnection, "select * from sites")

# Get the first two rows of data
data.frame = fetch(result, n = 2)
print(data.frame)

Other Extensions