gpt4 book ai didi

mysql - Perl DBI 创建表错误

转载 作者:太空宇宙 更新时间:2023-11-03 12:17:14 25 4
gpt4 key购买 nike

我正在尝试使用 DBI 在 perl 中创建表。这是我的代码:

#! usr/bin/perl
use strict;
use warnings;
use 5.014;
use DBI;

my $user = "user";
my $password = "password";
my $hostname = "localhost";
my $database = 'database';

my $dsn = "DBI:mysql:database=$database;host=$hostname;";
my $dbh = DBI->connect($dsn, $user, $password, { RaiseError => 1});


my $create = "-- drop table if exists DEVICE;
CREATE TABLE DEVICE(
NAME CHAR(60),
ID CHAR(36) NOT NULL,
SHORT_ID INT UNSIGNED,
MODEL CHAR(50),
SERIAL_NUMBER VARCHAR(50),
IP_ADDRESS CHAR(50),
LOCATION CHAR(50),
DV_VERSION CHAR(20),
OS_VERSION CHAR(20),
DEVICE_GROUP CHAR(50),
MANAGED TINYINT NOT NULL,
CONSTRAINT PK_DEVICE PRIMARY KEY (ID)
) Engine = InnoDB;

-- drop table if exists SEGMENT;
CREATE TABLE SEGMENT(
ID CHAR(100) NOT NULL,
DEVICE_ID CHAR(36) NOT NULL,
NAME CHAR(60),
IP_ADDRESS CHAR(50),
SLOT_INDEX INT NOT NULL,
SEGMENT_INDEX INT NOT NULL,
CONSTRAINT PK_SEGMENT PRIMARY KEY (ID),
KEY(DEVICE_ID),
CONSTRAINT FK_PARENT_DEV FOREIGN KEY (DEVICE_ID) REFERENCES DEVICE(ID)
) Engine = InnoDB;
";
my $sth = $dbh->prepare($create);
$sth->execute;

运行时出现以下错误:

DBD::mysql::st execute failed: 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 'CREATE TABLE SEGMENT(
ID CHAR(100) NOT NULL,
DEVICE' at line 18 at file.pl line 47.

当我直接在 mysql 中复制/粘贴与 $create 中完全相同的字符串时,它工作得很好。

请帮忙,谢谢。

编辑:抱歉,我应该澄清一下。我正在从另一个来源提取 $create 中的内容。这只是其中的一部分,我无法编辑文本。我想我需要知道如何将它拆分成一个数组并一个一个地执行它们。

最佳答案

DBI 连接不像 MySQL 命令行工具那样工作。您不能同时执行多个 SQL 语句,并且不需要 -- 前缀。

(还值得注意的是,您颠倒了大写 SQL 语言单词和小写标识符的约定。)

像这样的代码应该可以工作

$dbh->do("drop table if exists DEVICE");
$dbh->do(<<__ENDSQL__);
CREATE TABLE DEVICE(
NAME CHAR(60),
ID CHAR(36) NOT NULL,
SHORT_ID INT UNSIGNED,
MODEL CHAR(50),
SERIAL_NUMBER VARCHAR(50),
IP_ADDRESS CHAR(50),
LOCATION CHAR(50),
DV_VERSION CHAR(20),
OS_VERSION CHAR(20),
DEVICE_GROUP CHAR(50),
MANAGED TINYINT NOT NULL,
CONSTRAINT PK_DEVICE PRIMARY KEY (ID)
) Engine = InnoDB
__ENDSQL__

$dbh->do("drop table if exists SEGMENT");
$dbh->do(<<__ENDSQL__);
CREATE TABLE SEGMENT(
ID CHAR(100) NOT NULL,
DEVICE_ID CHAR(36) NOT NULL,
NAME CHAR(60),
IP_ADDRESS CHAR(50),
SLOT_INDEX INT NOT NULL,
SEGMENT_INDEX INT NOT NULL,
CONSTRAINT PK_SEGMENT PRIMARY KEY (ID),
KEY(DEVICE_ID),
CONSTRAINT FK_PARENT_DEV FOREIGN KEY (DEVICE_ID) REFERENCES DEVICE(ID)
) Engine = InnoDB
__ENDSQL__

关于mysql - Perl DBI 创建表错误,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/21589140/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com