when use pdo to create sqlite3 db, how to set up the character set ?

blackhorse


$db = new PDO('sqlite:blog.db');

     $query = "
CREATE TABLE blog (id integer  NOT NULL  PRIMARY KEY AUTOINCREMENT DEFAULT 0, blog_id Varchar(255) DEFAULT NULL, title Varchar(100) DEFAULT NULL, description Varchar(255) DEFAULT NULL);
";

 $db->exec($query);

So I used this script to create a sqlite3 database. But how can I set up this database characer set to be utf8 when I create this database?

Thanks!

halojoy

Well,
I have worked a lot with SQLite3.
And what I know you can not set CHARSET.
SQLite is often compiled to use UTF-8 internally.

MySQL often use for text field: collation utf-8_general_ci
The _ci means CASE INSENSITIVE in search/compare of text.

To make SQLite FIELD be case insensitive we can use COLLATE NOCASE
you create each field like this:

$sql = "CREATE TABLE blog (
id integer  NOT NULL  PRIMARY KEY AUTOINCREMENT DEFAULT 0, 
blog_id Varchar(255) DEFAULT NULL COLLATE NOCASE, 
title Varchar(100) DEFAULT NULL COLLATE NOCASE, 
description Varchar(255) DEFAULT NULL COLLATE NOCASE
) " ;

blackhorse

How about

PRAGMA encoding='UTF-8';

I think with sqlite3 starts supporting both utf-8 and utf-16, that is this for? Only to tell utf8 or utf16, no other encoding else?