notas:bases_de_datos
¡Esta es una revisión vieja del documento!
Tabla de Contenidos
Bases De Datos
Equivalencias MySQL - PostgreSQL
Perl DBI
MySQL | PostgreSQL | ||
---|---|---|---|
$db=DBI→connect(“dbi:mysql:database= … ) | $db=DBI→connect(“dbi:Pg:database= … ) |
Sintaxis y lenguaje
MySQL | PostgreSQL | ||
---|---|---|---|
SHOW FULL PROCESSLIST / SHOW PROCESSLIST | SELECT * FROM pg_stat_activity | ||
# | – | ||
… WHERE lastname=“smith” | … WHERE lower(lastname)='smith' | ||
DESCRIBE tablename | \d tablename | ||
DROP TABLE IF EXISTS | DROP TABLE IF EXISTS | ||
SELECT … INTO OUTFILE '/var/tmp/outfile' | COPY ( SELECT … ) TO '/var/tmp/outfile' | ||
SHOW DATABASES | \l | ||
SHOW TABLES | \dt |
Tipos de datos
MySQL | PostgreSQL | comments | ||
---|---|---|---|---|
TINYINT SMALLINT MEDIUMINT BIGINT | SMALLINT SMALLINT INTEGER BIGINT | see [2]; integer size in PostgreSQL is 4 Bytes signed (-2147483648 – +2147483647) | ||
TINYINT UNSIGNED SMALLINT UNSIGNED MEDIUMINT UNSIGNED INT UNSIGNED BIGINT UNSIGNED | SMALLINT INTEGER INTEGER BIGINT NUMERIC(20) | SQL doesn't know UNSIGNED , all numbers are signed. | ||
ZEROFILL | No replacement. | |||
DOUBLE | DOUBLE PRECISION | |||
BOOLEAN | BOOLEAN | MySQL Booleans are an alias for TINYINT(1); PostgreSQL doesn't auto-convert numbers into booleans. | ||
DATE TIME DATETIME | DATE TIME TIMESTAMP | |||
TIMESTAMP NOT NULL DEFAULT NOW() | TIMESTAMP NOT NULL DEFAULT NOW() | |||
LONGTEXT MEDIUMTEXT BLOB | TEXT TEXT BYTEA | TEXT | ||
columnname INT AUTO_INCREMENT | columnname SERIAL equals to: CREATE SEQUENCE tablename_columnname_seq; CREATE TABLE tablename ( columnname INT4 NOT NULL DEFAULT nextval('tablename_columnname_seq') ); | SERIAL = 1 – 2147483647BIGSERIAL = 1 – 9223372036854775807 SERIAL is in fact an entity named SEQUENCE. It exists independently on the rest of your table. If you want to cleanup your system after dropping a table, you also have to DROP SEQUENCE tablename_columnname_seq . More on that topic... | ||
field ENUM (val_1, val_2, ...) | field VARCHAR(255) NOT NULL, CHECK (field IN (val_1, val_2, ...)) OR CREATE TYPE mood AS ENUM ('sad','ok','happy'); CREATE TABLE person ( current_mood mood ... | PostgreSQL doesn't have the ENUM types prior to 8.3, so you need to simulate it with contraints when using < 8.3. |
Ref.: http://en.wikibooks.org/wiki/Converting_MySQL_to_PostgreSQL
Tips MySQL
Borrar todas las tablas de una base de datos
MYSQL="mysql -h HOST -u USERNAME -pPASSWORD -D DB_NAME" $MYSQL -BNe "show tables" | awk '{print "set foreign_key_checks=0; drop table `" $1 "`;"}' | $MYSQL unset MYSQL
También podemos usar la sintaxys LIKE para borrar solo algunas tablas que concuerden con algún patrón, útil cuando tenemos un programa que genera muchas tablas con un timestamp añadido
show tables like 'ifvolume_1%'
Replicacion facil
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock old_passwords=1 log_bin = /var/log/mysql/mysql-bin.log relay-log = /var/log/mysql/mysql-relay.log max_binlog_size = 100M expire_logs_days = 2 max_binlog_size = 100M log-bin binlog-do-db=proxyauth skip-slave-start server-id=1 replicate-do-table=base.tabla master-host=10.1.1.1 master-user=root master-password=contraseña master-port=3306 [mysql.server] user=mysql basedir=/var/lib
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock old_passwords=1 log_bin = /var/log/mysql/mysql-bin.log relay-log = /var/log/mysql/mysql-relay.log max_binlog_size = 100M expire_logs_days = 2 max_binlog_size = 100M log-bin binlog-do-db=proxyauth skip-slave-start server-id=2 replicate-do-table=base.tabla master-host=10.1.1.2 master-user=root master-password=contraseña master-port=3306 [mysql.server] user=mysql basedir=/var/lib
skip-slave-start Es para tener que hacer el start slave; manualmente (por si las dudas).
notas/bases_de_datos.1277400949.txt.gz · Última modificación: 2010/06/24 17:35 por cayu