Main Content

mysql

Create MySQL native interface database connection

Description

example

conn = mysql(datasource,username,password) creates a MySQL® native interface database connection using the specified data source, user name, and password. conn is a connection object.

example

conn = mysql(username,password,Name,Value) creates a MySQL native interface database connection using the specified user name and password, with additional options specified by one or more name-value pair arguments. For example, "Server","dbtb00" specifies the database server name as dbtb00.

Examples

collapse all

Create a MySQL® native interface connection to a MySQL database. Then, import data from the database into MATLAB® and perform simple data analysis. Close the database connection.

This example assumes that you are connecting to a MySQL database version 5.7.22 using the MySQL Connector/C++ driver version 8.0.15.

Connect to the database using the data source name, user name, and password.

datasource = "MySQLNative";
username = "root";
password = "matlab";

conn = mysql(datasource,username,password)
conn = 
  connection with properties:

                  DataSource: "MySQLNative"
                    UserName: "root"

  Database Properties:

                  AutoCommit: "on"
                LoginTimeout: 0
      MaxDatabaseConnections: 151

  Catalog and Schema Information:

              DefaultCatalog: "toystore_doc"
                    Catalogs: ["information_schema", "mysql", "performance_schema" ... and 3 more]
                     Schemas: []

  Database and Driver Information:

         DatabaseProductName: "MySQL"
      DatabaseProductVersion: "5.7.22"
                  DriverName: "MySQL Connector/C++"
               DriverVersion: "8.0.15"

The property sections of the connection object are:

  • Database Properties — Information about the database configuration

  • Catalog and Schema Information — Names of catalogs and schemas in the database

  • Database and Driver Information — Names and versions of the database and driver

Import all data from the table inventoryTable into MATLAB using the sqlread function. Display the first three rows of data.

tablename = "inventoryTable";
data = sqlread(conn,tablename);
head(data,3)
ans=3×4 table
    productNumber    Quantity    Price        inventoryDate    
    _____________    ________    _____    _____________________

          1            1700      14.5     "2014-09-23 09:38:34"
          2            1200         9     "2014-07-08 22:50:45"
          3             356        17     "2014-05-14 07:14:28"

Determine the highest product quantity from the table.

max(data.Quantity)
ans = 9000

Close the database connection conn.

close(conn)

Create a MySQL® native interface connection to a MySQL database using name-value pair arguments. Then, import data from the database into MATLAB® and perform simple data analysis. Close the database connection.

This example assumes that you are connecting to a MySQL database version 5.7.22 using the MySQL Connector/C++ driver version 8.0.15.

Connect to the database using the user name and password shown. Specify the database server name dbtb01, database name toystore_doc, and port number 3306 by setting the corresponding name-value pair arguments.

username = "root";
password = "matlab";

conn = mysql(username,password,'Server',"dbtb01", ...
    'DatabaseName',"toystore_doc",'PortNumber',3306)
conn = 
  connection with properties:

                    Database: "toystore_doc"
                    UserName: "root"

  Database Properties:

                  AutoCommit: "on"
                LoginTimeout: 0
      MaxDatabaseConnections: 151

  Catalog and Schema Information:

              DefaultCatalog: "toystore_doc"
                    Catalogs: ["information_schema", "mysql", "performance_schema" ... and 3 more]
                     Schemas: []

  Database and Driver Information:

         DatabaseProductName: "MySQL"
      DatabaseProductVersion: "5.7.22"
                  DriverName: "MySQL Connector/C++"
               DriverVersion: "8.0.15"

The property sections of the connection object are:

  • Database Properties — Information about the database configuration

  • Catalog and Schema Information — Names of catalogs and schemas in the database

  • Database and Driver Information — Names and versions of the database and driver

Import all data from the table inventoryTable into MATLAB using the sqlread function. Display the first three rows of data.

tablename = "inventoryTable";
data = sqlread(conn,tablename);
head(data,3)
ans=3×4 table
    productNumber    Quantity    Price        inventoryDate    
    _____________    ________    _____    _____________________

          1            1700      14.5     "2014-09-23 09:38:34"
          2            1200         9     "2014-07-08 22:50:45"
          3             356        17     "2014-05-14 07:14:28"

Determine the highest product quantity from the table.

max(data.Quantity)
ans = 9000

Close the database connection conn.

close(conn)

Input Arguments

collapse all

Data source name, specified as a character vector or string scalar. Specify the name of an existing data source.

Example: "myDataSource"

Data Types: char | string

User name required to access the database, specified as a character vector or string scalar. If no user name is required, specify an empty value "".

Data Types: char | string

Password required to access the database, specified as a character vector or string scalar. If no password is required, specify an empty value "".

Data Types: char | string

Name-Value Pair Arguments

Specify optional comma-separated pairs of Name,Value arguments. Name is the argument name and Value is the corresponding value. Name must appear inside quotes. You can specify several name and value pair arguments in any order as Name1,Value1,...,NameN,ValueN.

Example: conn = mysql(username,password,"Server","dbtb01","PortNumber",3306,"DatabaseName","toystore_doc") creates a MySQL native interface database connection using the database server dbtb01, port number 3306, and database name toystore_doc.

Database server name or address, specified as the comma-separated pair consisting of 'Server' and a string scalar or character vector.

Example: "dbtb00"

Data Types: char | string

Port number, specified as the comma-separated pair consisting of 'PortNumber' and a numeric scalar.

Example: 3306

Data Types: double

Database name, specified as the comma-separated pair consisting of 'DatabaseName' and a string scalar or character vector. If you do not specify a database name, the mysql function connects to the default database on the database server.

Example: "toystore_doc"

Data Types: char | string

Introduced in R2020b