Data Description and Creation

Now that we know which databases and tables we are using, we can look at differences in how we specify various features during the creation process.

Creating Databases to Work with UTF-8

To create databases that understand UTF-8 input and store string values as Unicode text in tables, we use the following queries. We also use these to specify the default sorting order (collation) for these new databases:

Server	Syntax
MySQL	CREATE DATABASE DBName DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
PostgreSQL	CREATE DATABASE MooCow WITH ENCODING 'UNICODE' or initdb -E UNICODE from command line.
Oracle	When Creating Database, specify UTF8 or AL32UTF8 as CHARACTER SET
Microsoft SQL Server	Done at installation time.

Setting the Connection to UTF-8

Even if the database server is set up to use UTF-8 correctly, we need to ensure that the connection is set up correctly:

Server	Syntax
MySQL	SET NAMES 'utf8'
PostgreSQL	SET NAMES 'utf8' or SET CLIENT_ENCODING TO 'utf8'
Oracle	Set the NLS_LANG environment variable to AL32UTF8 or simply UTF8. Do this before starting Apache or IIS.
Microsoft SQL Server	Make sure freeTDS is configured properly.

Auto-Incrementing Fields

Fields that have an auto-incrementing index after every row insertion are quite different from server to server:

Server	Syntax
MySQL	AUTO_INCREMENT
PostgreSQL	SERIAL, BIGSERIAL datatypes
Oracle	CREATE SEQUENCE supplier_seq MINVALUE 1 MAXVALUE 999999999999999999999999999 START WITH 1 INCREMENT BY 1 CACHE 20; sequence.nextval is critical use of feature. INSERT INTO suppliers (supplier_id, supplier_name) VALUES (supplier_seq.nextval, 'Some Company');
Microsoft SQL Server	IDENTITY

Date/Time Column Types

To create a column of a date or time type, you must change your SQL from server to server:

Server	Syntax
MySQL	DATE, TIME, DATETIME
PostgreSQL	DATE, TIME, TIMESTAMP
Oracle	DATE (does DATE and times), TIMESTAMP (date with more precision) and INTERVAL HOURS TO SECONDS (HH:MM:SS), INTERVAL YEAR TO DAY (YYYY-MM-DD), TIME datatype exists too.
Microsoft SQL Server	DATETIME, SMALLDATETIME only

Binary Column Types

To store binary data in your database, you need to use a special data type:

Server	Syntax
MySQL	BLOB
PostgreSQL	bytea
Oracle	BLOB
Microsoft SQL Server	image

Large Text Column Types

Because most CHAR and VARCHAR columns are limited to 255 characters, you sometimes need larger text column types. These are as follows:

Server	Syntax
MySQL	TEXT
PostgreSQL	TEXT
Oracle	CLOB
Microsoft SQL Server	TEXT

Bulk Data Insertion

Inserting data into your database outside of PHP code can sometimes be done by using what is known as bulk data insertion. It is very specific to servers:

Server	Syntax
MySQL	LOAD DATA and LOAD DATA LOCAL
PostgreSQL	COPY TableName (col1,...,coln) FROM FILENAME WITH ...
Oracle	Control File: LOAD DATA INFILE <dataFile> APPEND INTO TABLE <tableName> FIELDS TERMINATED BY '<separator>' (<list of all attribute names to load>) and then data file
Microsoft SQL Server	You will have to do this by hand or in code.