54、MySQL数据库与MATLAB的数据交互：导入与插入操作详解

最新推荐文章于 2025-07-04 12:45:52 发布

白露未晞593

最新推荐文章于 2025-07-04 12:45:52 发布

阅读量60

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库工具箱：MATLAB数据操作指南文章标签： MySQL MATLAB sqlread

本文链接：https://blog.youkuaiyun.com/bash7scripter/article/details/149382116

数据库工具箱：MATLAB数据操作指南专栏收录该内容

76 篇文章 ¥499.90

订阅专栏¥69.90

会员秒杀 ¥9.9 重磅福利

超级会员免费看

MySQL数据库与MATLAB的数据交互：导入与插入操作详解

1. sqlread函数：从MySQL数据库表导入数据到MATLAB

1.1 语法

data = sqlread(conn,tablename) ：从MySQL数据库表将数据导入到MATLAB并返回一个表，等同于执行 SELECT * FROM tablename 的SQL语句。
data = sqlread(conn,tablename,opts) ：使用 SQLImportOptions 对象自定义从数据库表导入数据的选项。
data = sqlread( ___ ,Name,Value) ：使用一个或多个名称 - 值对参数指定额外选项。
[data,metadata] = sqlread( ___ ) ：还返回包含导入数据元信息的元数据表。

1.2 输入参数

参数	描述	类型
`conn`	MySQL原生接口数据库连接	连接对象
`tablename`	数据库表名	字符向量或字符串标量
`opts`	数据库导入选项	`SQLImportOptions` 对象

1.3 名称 - 值对参数

参数	描述	类型	示例
`Catalog`	数据库目录名	字符向量或字符串标量	`'Catalog','toy_store'`
`MaxRows`	要返回的最大行数	正数值标量	`'MaxRows',10`
`VariableNamingRule`	变量命名规则	`"preserve"` （默认）或 `"modify"`	`'VariableNamingRule',"modify"`

1.4 输出参数

参数	描述	类型
`data`	导入的数据	表
`metadata`	元信息	表

1.5 示例

1.5.1 使用MySQL原生接口从数据库表导入数据

datasource = "MySQLNative";
username = "root";
password = "matlab";
conn = mysql(datasource,username,password);
tablename = "productTable";
data = sqlread(conn,tablename);
head(data,3)
close(conn)

1.5.2 使用导入选项从数据库表导入数据

datasource = "MySQLNative";
username = "root";
password = "matlab";
conn = mysql(datasource,username,password);
patients = readtable("patients.xls");
tablename = "patients";
sqlwrite(conn,tablename,patients)
opts = databaseImportOptions(conn,tablename)
vars = opts.SelectedVariableNames;
varOpts = getoptions(opts,vars)
opts = setoptions(opts,{'Gender','Location','SelfAssessedHealthStatus'}, 'Type','categorical');
opts = setoptions(opts,'Smoker','Type','double');
varOpts = getoptions(opts,{'Gender','Location','Smoker', 'SelfAssessedHealthStatus'})
data = sqlread(conn,tablename,opts);
tail(data)
summary(data)
sqlquery = strcat("DROP TABLE ",tablename);
execute(conn,sqlquery)
close(conn)

1.5.3 限制导入数据的行数

datasource = "MySQLNative";
username = "root";
password = "matlab";
conn = mysql(datasource,username,password);
tablename = "productTable";
data = sqlread(conn,tablename,'MaxRows',10);
data(1:3,:)
data.productDescription(1:3)
column = "productDescription";
data = sortrows(data,column);
data.productDescription(1:3)
close(conn)

1.5.4 检索导入数据的元信息

datasource = "MySQLNative";
username = "root";
password = "matlab";
conn = mysql(datasource,username,password);
outages = readtable("outages.csv");
tablename = "outages";
sqlwrite(conn,tablename,outages, 'ColumnType',["varchar(120)","datetime","numeric(38,16)", "numeric(38,16)","datetime","varchar(150)"]);
[data,metadata] = sqlread(conn,tablename);
metadata.Properties.RowNames
metadata.VariableType
metadata.FillValue
metadata.MissingRows
index = metadata.MissingRows{5,1};
nullrestoration = data(index,:);
head(nullrestoration)
sqlstr = "DROP TABLE ";
sqlquery = strcat(sqlstr,tablename);
execute(conn,sqlquery)
close(conn)

1.6 限制

使用 'VariableNamingRule' 名称 - 值对参数与 SQLImportOptions 对象 opts 时， sqlread 函数会返回错误。
当 'VariableNamingRule' 名称 - 值对参数设置为 'modify' 时：
变量名 Properties 、 RowNames 和 VariableNames 是表数据类型的保留标识符。
每个变量名的长度必须小于 namelengthmax 返回的数字。

1.7 MySQL数据类型与MATLAB数据类型映射

MySQL数据类型	MATLAB数据类型
`BIT`	`logical`
`TINYINT`	`double`
`SMALLINT`	`double`
`BIGINT`	`double`
`REAL`	`double`
`DOUBLE`	`double`
`DECIMAL`	`double`
`NUMERIC`	`double`
`CHAR`	`string`
`VARCHAR`	`string`
`LONGVARCHAR`	`string`
`TIMESTAMP`	`datetime`
`DATE`	`datetime`
`TIME`	`duration`
`YEAR`	`double`
`ENUM`	`categorical`
`JSON`	`char`

1.8 元数据表变量

变量名	变量描述	变量数据类型
`VariableType`	导入数据中每个变量的数据类型	字符向量的元胞数组
`FillValue`	导入数据中每个变量的缺失数据值	缺失数据值的元胞数组
`MissingRows`	导入数据中每个变量的缺失数据的索引	数值索引的元胞数组

1.9 流程图

graph TD;
    A[创建数据库连接] --> B[指定表名];
    B --> C{是否使用选项};
    C -- 是 --> D[使用SQLImportOptions对象或名称 - 值对];
    C -- 否 --> E[直接导入数据];
    D --> E;
    E --> F[获取导入数据];
    F --> G{是否需要元信息};
    G -- 是 --> H[获取元信息];
    G -- 否 --> I[结束];
    H --> I;

2. sqlwrite函数：将MATLAB数据插入到MySQL数据库表

2.1 语法

sqlwrite(conn,tablename,data) ：将MATLAB表中的数据插入到数据库表中。如果表已存在，则将MATLAB表中的数据作为行追加到现有数据库表中；如果表不存在，则创建一个具有指定表名的新表，并将数据作为行插入到新表中。
sqlwrite(conn,tablename,data,Name,Value) ：使用一个或多个名称 - 值对参数指定额外选项。

2.2 输入参数

参数	描述	类型
`conn`	MySQL原生接口数据库连接	连接对象
`tablename`	数据库表名	字符向量或字符串标量
`data`	要插入的数据	表

2.2.1 MATLAB表中的有效数据类型

数值数组：可以包含 int8 、 uint8 、 int16 、 uint16 、 int32 、 uint32 、 int64 、 uint64 、 single 、 double 等数据类型。
数值数组的元胞数组
字符向量的元胞数组
字符串数组
日期时间数组
持续时间数组
逻辑数组
逻辑数组的元胞数组

2.2.2 日期和时间数据的支持格式

日期： 'yyyy-MM-dd'
时间： 'hh:mm:ss'
时间戳： 'yyyy-MM-dd HH:mm:ss'

2.3 名称 - 值对参数

参数	描述	类型	示例
`Catalog`	数据库目录名	字符向量或字符串标量	`'Catalog','toy_store'`
`ColumnType`	数据库列类型	字符向量、字符串标量、字符向量的元胞数组或字符串数组	`'ColumnType',["numeric" "varchar(400)"]`

2.4 示例

2.4.1 使用MySQL原生接口将数据追加到现有表中

datasource = "MySQLNative";
username = "root";
password = "matlab";
conn = mysql(datasource,username,password);
tablename = "productTable";
rows = sqlread(conn,tablename);
tail(rows,3)
data = table(30,500000,1000,25,"Rubik's Cube", 'VariableNames',["productNumber" "stockNumber" "supplierNumber" "unitCost" "productDescription"]);
sqlwrite(conn,tablename,data)
rows = sqlread(conn,tablename);
tail(rows,4)
close(conn)

2.4.2 将数据插入到新表中

datasource = "MySQLNative";
username = "root";
password = "matlab";
conn = mysql(datasource,username,password);
data = table([30;40],[500000;600000],[1000;2000],[25;30], ["Rubik's Cube";"Doll House"],'VariableNames',["productNumber" "stockNumber" "supplierNumber" "unitCost" "productDescription"]);
tablename = "toyTable";
sqlwrite(conn,tablename,data)
rows = sqlread(conn,tablename)
close(conn)

2.4.3 插入数据到新表时指定列类型

datasource = "MySQLNative";
username = "root";
password = "matlab";
conn = mysql(datasource,username,password);
data = table([30;40],[500000;600000],[1000;2000],[25;30], ["Rubik's Cube";"Doll House"],'VariableNames',["productNumber" "stockNumber" "supplierNumber" "unitCost" "productDescription"]);
tablename = "toyTable";
coltypes = ["numeric" "numeric" "numeric" "numeric" "varchar(255)"];
sqlwrite(conn,tablename,data,'ColumnType',coltypes)
rows = sqlread(conn,tablename)
close(conn)

2.5 数据类型匹配

2.5.1 现有表的数据类型匹配

MATLAB表变量的数据类型	现有数据库列的数据类型
数值数组或数值数组的元胞数组	`INTEGER` 、 `SMALLINT` 、 `DECIMAL` 、 `NUMERIC` 、 `FLOAT` 、 `REAL` 、 `DOUBLE PRECISION`
字符向量的元胞数组、字符串数组、日期时间数组或持续时间数组	`DATE` 、 `TIME` 、 `TIMESTAMP`
逻辑数组或逻辑数组的元胞数组	`BIT`
字符向量的元胞数组或字符串数组	`CHAR` 、 `VARCHAR`

2.5.2 新表的数据类型匹配

MATLAB表变量的数据类型	数据库列的默认数据类型
`int8` 数组	`TINYINT`
`int16` 数组	`SMALLINT`
`int32` 数组	`INTEGER`
`int64` 数组	`BIGINT`
逻辑数组	`BIT`
`single` 或 `double` 数组	`NUMERIC`
日期时间数组	`TIMESTAMP`
持续时间数组	`TIME`
字符向量的元胞数组或字符串数组	`VARCHAR`

2.6 接受的缺失数据

MATLAB表变量的数据类型	数据库列的数据类型	接受的缺失数据
日期时间数组	日期或时间戳	`NaT`
持续时间数组	时间	`NaN`
`double` 或 `single` 数组或 `double` 或 `single` 数组的元胞数组	数值	`NaN` 、 `[]` 或 `''`
字符向量的元胞数组	日期或时间戳	`'NaT'` 或 `''`
字符向量的元胞数组	时间	`'NaN'` 或 `''`
字符向量的元胞数组	`Char` 、 `Varchar` 或其他文本数据类型	`''`
字符串数组	日期或时间戳	`""` 、 `"NaT"` 或 `missing`
字符串数组	时间	`""` 、 `"NaN"` 或 `missing`
字符串数组	`Char` 、 `Varchar` 或其他文本数据类型	`missing`

2.7 流程图

graph TD;
    A[创建数据库连接] --> B[指定表名和数据];
    B --> C{表是否存在};
    C -- 是 --> D[追加数据];
    C -- 否 --> E[创建新表并插入数据];
    D --> F{是否使用额外选项};
    E --> F;
    F -- 是 --> G[使用名称 - 值对参数];
    F -- 否 --> H[结束];
    G --> H;

综上所述， sqlread 和 sqlwrite 函数为MATLAB与MySQL数据库之间的数据交互提供了强大而灵活的工具。通过合理使用这些函数及其相关选项，可以方便地实现数据的导入和插入操作，同时处理各种数据类型和缺失数据情况。在实际应用中，根据具体需求选择合适的函数和参数，能够高效地完成数据库与MATLAB之间的数据传输和处理任务。