enum(列挙型)
戻る
<![CDATA[
# obtain_enum.pl
use strict;
use DBI;
my ( %t, $n, @rec, @list);
$t{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$t{dbh} = DBI->connect($t{dsn}, "cbuser", "cbpass") or die "Cannot connect to server\n";
$t{dbh}->do("SET NAMES sjis");
if(!$t{dbh}){
print "SQL read ERROR!\n";
exit;
}
$t{sth} = $t{dbh}->prepare("SHOW COLUMNS FROM et LIKE 'e'");
$t{sth}->execute;
while (@rec = $t{sth}->fetchrow_array) {
print $rec[0],"==>$rec[1]\n";
@list = split(/,/,$rec[1]);
$t{first1} = $list[0];
$t{first1} =~ s/enum\(//;
$t{first1} =~ s/\'//g;
print 0,"==>",$t{first1},"\n";
for $n ( 1 .. ($#list-1) ) {
$list[$n] =~ s/\'//g;
print $n,"==>",$list[$n],"\n";
}
$t{last1} = $list[$#list];
$t{last1} =~ s/\)//;
$t{last1} =~ s/\'//g;
print $#list,"==>",$t{last1},"\n";
}
$t{sth}->finish;
$t{dbh}->disconnect;
__END__
C:\database\perl>perl obtain_enum.pl
e==>enum('p0','p1','p2','p10','p20','This is an example of enum.')
0==>p0
1==>p1
2==>p2
3==>p10
4==>p20
5==>This is an example of enum.
--------------------------------------------------------------------
# テーブルを生成
open(OUT,">../sql/et.sql");
print OUT 'DROP TABLE IF EXISTS et;',"\n";
print OUT 'CREATE TABLE et',"\n";
print OUT '(',"\n";
print OUT ' id INT AUTO_INCREMENT,',"\n";
print OUT ' e enum("p0") DEFAULT "p0" NOT NULL,',"\n";
print OUT ' PRIMARY KEY (id)',"\n";
print OUT ');',"\n\n";
# 項目を追加
print OUT 'alter table et modify e enum("p0","p1","p2","p10","p20");',"\n";
mysql> show columns from et like 'e';
+-------+----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------------------+------+-----+---------+-------+
| e | enum('p0','p1','p2','p10','p20') | YES | | NULL | |
+-------+----------------------------------+------+-----+---------+-------+
--------------------------------------------------------------------
show columns from et like e;
ERROR 1064 (42000): 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 'e' at
line 1
--------------------------------------------------------------------
DROP TABLE IF EXISTS et;
CREATE TABLE et
(
id INT AUTO_INCREMENT,
e enum("p0"),
PRIMARY KEY (id)
);
INSERT INTO et (e) VALUES("p0");
alter table et modify e enum("p0","p1","p2");
INSERT INTO et (e) VALUES("p1");
INSERT INTO et (e) VALUES("p2");
show columns from et;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| e | enum('p0','p1','p2') | YES | | NULL | |
+-------+----------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
select * from et;
+----+------+
| id | e |
+----+------+
| 1 | p0 |
| 2 | p1 |
| 3 | p2 |
+----+------+
3 rows in set (0.00 sec)
--------------------------------------------------------------------
DROP TABLE IF EXISTS et;
CREATE TABLE et
(
id INT AUTO_INCREMENT,
e enum("p12","p2"),
PRIMARY KEY (id)
);
INSERT INTO et (e) VALUES("p2");
show columns from et;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| e | enum('p12','p2') | YES | | NULL | |
+-------+------------------+------+-----+---------+----------------+
select * from et;
+----+------+
| id | e |
+----+------+
| 1 | p2 |
+----+------+
--------------------------------------------------------------------
e enum("p12","p2"),
PRIMARY KEY (id)
);
INSERT INTO et (e) VALUES("p1");
ERROR 1265 (01000) at line 9: Data truncated for column 'e' at row 1
--------------------------------------------------------------------
e enum("p12","p2"),
INSERT INTO et (e) VALUES("p1","p2");
ERROR 1136 (21S01) at line 9: Column count doesn't match value count at row 1
--------------------------------------------------------------------
]]>
戻る