php通用数据库db类Medoo使用

medoo是一个小巧，但是防止注入的安全的数据库通用的类库。没有orm。
推荐在小型项目中使用。
此类还有一个特点，拼合sql语句很方便，且各种join都好使。
此外，它还有一个超有用的log函数，可以打印执行过的sql语句。爱不释手有没有！

composer

"catfan/medoo":"1.4.5"

需php5.4

先建表

CREATE TABLE `test_databases` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `db_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '库名',
  `user_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '测试用户id',
  `created_at` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间',
  `updated_at` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '修改时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

先自行插入2条数据。

示例程序

<?php
namespace app\control;
use Medoo\Medoo as me;
class Medoo {
    public function index($req, $res, $args) {
        $database = new me ( [
            // 必须
            'database_type' => 'mysql',
            'database_name' => 'test1',
            'server' => '127.0.0.1',
            'username' => 'root',
            'password' => 'root',
            
            // 可选，但最好加上
            'charset' => 'utf8mb4',
            'port' => 3306,
            
            // [optional] Enable logging (Logging is disabled by default for better performance)
            'logging' => true,
            
            // [optional] driver_option for connection, read more from http://www.php.net/manual/en/pdo.setattribute.php
            'option' => [ 
                \PDO::ATTR_STRINGIFY_FETCHES => false,
                \PDO::ATTR_EMULATE_PREPARES => false 
            ] 
        ]
         );
        echo "<h1>php通用数据库db类Medoo使用示例</h1>";
        $datas = $database->select ( "test_databases", [ 
            "db_name",
            "user_id" 
        ], [ 
            "id[<]" => 3 
        ] );
        var_dump ( $datas );
        echo "<hr>";
        $datas = $database->select ( "test_databases", '*', [ 
            "id[<]" => 3 
        ] );
        var_dump ( $datas );
        echo "<hr>";
        
        // 这种写法返回的是普通的二维数组
        $datas = $database->select ( "test_databases", [ 
            "db_name" 
        ], [ 
            "id[<]" => 3 
        ] );
        var_dump ( $datas );
        echo "<hr>";
        // 这种写法返回的是一维的数组，很简单
        $datas = $database->select ( "test_databases", "db_name", [ 
            "id[<]" => 3 
        ] );
        var_dump ( $datas );
        echo "<hr>";
        // 原生sql防止注入
        echo "<h3>原生sql使用示例</h3>";
        $sql = "select * from test_databases where id < :id  limit 2";
        $datas = $database->query ( $sql, [ 
            ":id" => 3 
        ] )->fetchAll ( \PDO::FETCH_ASSOC );
        var_dump ( $datas );
        echo "<hr>";
        $user_id = mt_rand ( 10000, 99999 );
        $database->insert ( "test_databases", [ 
            "db_name" => mt_rand ( 10000, 99999 ),
            "user_id" => $user_id 
        ] );
        $last_id = $database->id ();
        echo "<h3>刚插入后的数据</h3>";
        var_dump ( $last_id . ": user_id=" . $user_id );
        echo "<hr>";
        
        $data = $database->update ( "test_databases", [ 
            "user_id[+]" => 1 
        ], [ 
            "id" => $last_id 
        ] );
        
        // Returns the number of rows affected by the last SQL statement
        echo "受影响行数" . $data->rowCount ();
        echo "<hr>";
        
        // 这种写法返回的是一维的数组，很简单
        $datas = $database->select ( "test_databases", "user_id", [ 
            "id" => $last_id 
        ] );
        echo "<h3>user_id加1后的数据</h3>";
        var_dump ( $datas );

        //打印所有sql语句
        var_dump($database->log());
        return $res;
    }
}

浏览器效果