We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

Mysql UUID as id

Hello,

I want to use UUID as the id and primary key in my tables/Models.

I tried a database before insert trigger but threw an error. One solution is to use an auto increment id then an additional uuid field I guess.

The other solution I was working on was in the model with beforeValidationOnCreate() but the only way I could find to create a UUID was to setup another connection. Using Models Manager threw a syntax errror

    public function beforeValidationOnCreate()
    {

        //Db credentials

        $db_config = array(
            "host" => "localhost",
            "username" => "xxx",
            "password" => "xxx",
            "dbname" => "xxx"
        );

        // Create a connection
        $this->conn = new \Phalcon\Db\Adapter\Pdo\Mysql($db_config);

        try {

            $query = $this->conn->query("SELECT UUID()");
            $result = $query->fetch();
            $this->id = $result[0];
            $this->date_entered = date('Y-m-d H:i:s');

        } catch (PDOException $e) {

            print_r($e->getMessage());

        }

    }

While this works and I can use in a base model class and extend the ohters it seems like there should be a better way. Any ideas?

Thanks

edited Nov '15

Hi!

Some time ago i try to resolve same problem - use mysql and uuid. After learning many tech. doc, forums and Percona's blog - no better way :( And want say: unfortunately uuid is not fast solution and don't give 100% unique :(. About speed of uuid read https://www.percona.com/blog/2014/12/19/store-uuid-optimized-way/

One what you may to add - loop for generation unique uuid.

It's actually only for mysql and forks



4.7k
Accepted
answer

Thanks for the reply. This application is only going to have thousands or possibly tens of thousands of records per table so the small speed increase isn't a big deal for me. For ease I think I'm going to use a regular auto increment with a trigger to create a UUID. I am going to be using the id in some parameters in the url so that is why I didn't want to use the auto-increment value. This also prevents the additional query to generate the UUID.