Phalcon查詢語言（PHQL） · Phalcon 3.4中文手冊

[TOC] # Phalcon查詢語言（PHQL） Phalcon查詢語言，PhalconQL或簡稱PHQ??L是一種高級，面向對象的SQL方言，允許使用標準化的類SQL語言編寫查詢。PHQL實現為解析器（用C編寫），用于轉換目標RDBMS的語法。為了實現最高性能，Phalcon提供了一個使用與[SQLite](http://en.wikipedia.org/wiki/Lemon_Parser_Generator) 相同技術的解析器。該技術提供了一個內存小的解析器，內存占用非常少，同時也是線程安全的。解析器首先檢查傳遞PHQL語句的語法，然后構建語句的中間表示，最后將其轉換為目標RDBMS的相應SQL方言。在PHQL中，我們實現了一組功能，使您對數據庫的訪問更加安全： * 綁定參數是PHQL語言的一部分，可幫助您保護代碼 * PHQL僅允許每次調用執行一個SQL語句以防止注入 * PHQL忽略SQL注入中經常使用的所有SQL注釋 * PHQL僅允許數據操作語句，避免錯誤地或在未經授權的情況下外部更改或刪除表/數據庫 * PHQL實現了高級抽象，允許您將表作為模型和字段處理為類屬性 ## 用例為了更好地解釋PHQL如何工作，請考慮以下示例。我們有兩個模型`Cars`和`Brands`： ```php <?php use Phalcon\Mvc\Model; class Cars extends Model { public $id; public $name; public $brand_id; public $price; public $year; public $style; /** * 此模型映射到表sample_cars */ public function getSource() { return 'sample_cars'; } /** * 一輛車只有一個Brand，但一個Brand有很多Cars */ public function initialize() { $this->belongsTo('brand_id', 'Brands', 'id'); } } ``` 每輛車都有一個品牌，所以品牌有很多車： ```php <?php use Phalcon\Mvc\Model; class Brands extends Model { public $id; public $name; /** * 模型品牌被映射到'sample_brands'表 */ public function getSource() { return 'sample_brands'; } /** * 一個品牌可以有很多汽車 */ public function initialize() { $this->hasMany('id', 'Cars', 'brand_id'); } } ``` ## 創建PHQL查詢只需實例化 `Phalcon\Mvc\Model\Query`類即可創建PHQL查詢： ```php <?php use Phalcon\Mvc\Model\Query; // 實例化查詢 $query = new Query( 'SELECT * FROM Cars', $this->getDI() ); // 執行返回結果的查詢（如果有） $cars = $query->execute(); ``` 從控制器或視圖中，使用注入的`models manager`（`Phalcon\Mvc\Model\Manager`）可以輕松創建/執行它們： ```php <?php // 執行簡單的查詢 $query = $this->modelsManager->createQuery('SELECT * FROM Cars'); $cars = $query->execute(); // 帶有綁定參數 $query = $this->modelsManager->createQuery('SELECT * FROM Cars WHERE name = :name:'); $cars = $query->execute( [ 'name' => 'Audi', ] ); ``` 或者只是執行它： ```php <?php // 執行簡單的查詢 $cars = $this->modelsManager->executeQuery( 'SELECT * FROM Cars' ); // 帶有綁定參數 $cars = $this->modelsManager->executeQuery( 'SELECT * FROM Cars WHERE name = :name:', [ 'name' => 'Audi', ] ); ``` ## 選取記錄作為熟悉的SQL，PHQL允許使用我們所知道的SELECT語句查詢記錄，除了不使用指定表，我們使用模型類： ```php <?php $query = $manager->createQuery( 'SELECT * FROM Cars ORDER BY Cars.name' ); $query = $manager->createQuery( 'SELECT Cars.name FROM Cars ORDER BY Cars.name' ); ``` 命名空間中的類也是允許的： ```php <?php $phql = 'SELECT * FROM Formula\Cars ORDER BY Formula\Cars.name'; $query = $manager->createQuery($phql); $phql = 'SELECT Formula\Cars.name FROM Formula\Cars ORDER BY Formula\Cars.name'; $query = $manager->createQuery($phql); $phql = 'SELECT c.name FROM Formula\Cars c ORDER BY c.name'; $query = $manager->createQuery($phql); ``` PHQL支持大多數SQL標準，甚至是非標準指令，例如LIMIT： ```php <?php $phql = 'SELECT c.name FROM Cars AS c WHERE c.brand_id = 21 ORDER BY c.name LIMIT 100'; $query = $manager->createQuery($phql); ``` ### 結果類型根據我們查詢的列類型，結果類型會有所不同。如果檢索單個整個對象，則返回的對象是 `Phalcon\Mvc\Model\Resultset\Simple`。這種結果集是一組完整的模型對象： ```php <?php $phql = 'SELECT c.* FROM Cars AS c ORDER BY c.name'; $cars = $manager->executeQuery($phql); foreach ($cars as $car) { echo 'Name: ', $car->name, "\n"; } ``` 這完全相同： ```php <?php $cars = Cars::find( [ 'order' => 'name' ] ); foreach ($cars as $car) { echo 'Name: ', $car->name, "\n"; } ``` 可以修改完整對象并將其重新保存在數據庫中，因為它們代表關聯表的完整記錄。還有其他類型的查詢不返回完整對象，例如： ```php <?php $phql = 'SELECT c.id, c.name FROM Cars AS c ORDER BY c.name'; $cars = $manager->executeQuery($phql); foreach ($cars as $car) { echo 'Name: ', $car->name, "\n"; } ``` 我們只請求表中的某些字段，因此不能將它們視為整個對象，因此返回的對象仍然是 `Phalcon\Mvc\Model\Resultset\Simple`類型的結果集。但是，每個元素都是一個標準對象，只包含請求的兩列。這些不代表完整對象的值就是我們所說的標量。PHQL允許您查詢所有類型的標量：字段，函數，文字，表達式等： ```php <?php $phql = "SELECT CONCAT(c.id, ' ', c.name) AS id_name FROM Cars AS c ORDER BY c.name"; $cars = $manager->executeQuery($phql); foreach ($cars as $car) { echo $car->id_name, "\n"; } ``` 由于我們可以查詢完整的對象或標量，我們也可以同時查詢兩個： ```php <?php $phql = 'SELECT c.price*0.16 AS taxes, c.* FROM Cars AS c ORDER BY c.name'; $result = $manager->executeQuery($phql); ``` 在這種情況下的結果是對象 `Phalcon\Mvc\Model\Resultset\Complex`。這允許一次訪問完整對象和標量： ```php <?php foreach ($result as $row) { echo 'Name: ', $row->cars->name, "\n"; echo 'Price: ', $row->cars->price, "\n"; echo 'Taxes: ', $row->taxes, "\n"; } ``` 標量映射為每個“行”的屬性，而完整對象則映射為具有其相關模型名稱的屬性。 ### 連接使用PHQL從多個模型中請求記錄很容易。支持大多數種類的連接。當我們在模型中定義關系時，PHQL會自動添加這些條件： ```php <?php $phql = 'SELECT Cars.name AS car_name, Brands.name AS brand_name FROM Cars JOIN Brands'; $rows = $manager->executeQuery($phql); foreach ($rows as $row) { echo $row->car_name, "\n"; echo $row->brand_name, "\n"; } ``` 默認情況下，假定為INNER JOIN。您可以在查詢中指定JOIN的類型： ```php <?php $phql = 'SELECT Cars.*, Brands.* FROM Cars INNER JOIN Brands'; $rows = $manager->executeQuery($phql); $phql = 'SELECT Cars.*, Brands.* FROM Cars LEFT JOIN Brands'; $rows = $manager->executeQuery($phql); $phql = 'SELECT Cars.*, Brands.* FROM Cars LEFT OUTER JOIN Brands'; $rows = $manager->executeQuery($phql); $phql = 'SELECT Cars.*, Brands.* FROM Cars CROSS JOIN Brands'; $rows = $manager->executeQuery($phql); ``` 也可以手動設置JOIN的條件： ```php <?php $phql = 'SELECT Cars.*, Brands.* FROM Cars INNER JOIN Brands ON Brands.id = Cars.brands_id'; $rows = $manager->executeQuery($phql); ``` 此外，可以使用FROM子句中的多個表創建聯接： ```php <?php $phql = 'SELECT Cars.*, Brands.* FROM Cars, Brands WHERE Brands.id = Cars.brands_id'; $rows = $manager->executeQuery($phql); foreach ($rows as $row) { echo 'Car: ', $row->cars->name, "\n"; echo 'Brand: ', $row->brands->name, "\n"; } ``` 如果使用別名重命名查詢中的模型，那么將使用這些別名來命名結果的每一行中的屬性： ```php <?php $phql = 'SELECT c.*, b.* FROM Cars c, Brands b WHERE b.id = c.brands_id'; $rows = $manager->executeQuery($phql); foreach ($rows as $row) { echo 'Car: ', $row->c->name, "\n"; echo 'Brand: ', $row->b->name, "\n"; } ``` 當聯接模型與from模型具有多對多關系時，中間模型將隱式添加到生成的查詢中： ```php <?php $phql = 'SELECT Artists.name, Songs.name FROM Artists ' . 'JOIN Songs WHERE Artists.genre = "Trip-Hop"'; $result = $this->modelsManager->executeQuery($phql); ``` 此代碼在MySQL中執行以下SQL： ```sql SELECT `artists`.`name`, `songs`.`name` FROM `artists` INNER JOIN `albums` ON `albums`.`artists_id` = `artists`.`id` INNER JOIN `songs` ON `albums`.`songs_id` = `songs`.`id` WHERE `artists`.`genre` = 'Trip-Hop' ``` ### 聚合以下示例顯示如何在PHQL中使用聚合： ```php <?php // 所有車的價格是多少？ $phql = 'SELECT SUM(price) AS summatory FROM Cars'; $row = $manager->executeQuery($phql)->getFirst(); echo $row['summatory']; // 每個品牌有多少輛汽車？ $phql = 'SELECT Cars.brand_id, COUNT(*) FROM Cars GROUP BY Cars.brand_id'; $rows = $manager->executeQuery($phql); foreach ($rows as $row) { echo $row->brand_id, ' ', $row['1'], "\n"; } // 每個品牌有多少輛汽車？ $phql = 'SELECT Brands.name, COUNT(*) FROM Cars JOIN Brands GROUP BY 1'; $rows = $manager->executeQuery($phql); foreach ($rows as $row) { echo $row->name, ' ', $row['1'], "\n"; } $phql = 'SELECT MAX(price) AS maximum, MIN(price) AS minimum FROM Cars'; $rows = $manager->executeQuery($phql); foreach ($rows as $row) { echo $row['maximum'], ' ', $row['minimum'], "\n"; } // 統計不同的二手品牌 $phql = 'SELECT COUNT(DISTINCT brand_id) AS brandId FROM Cars'; $rows = $manager->executeQuery($phql); foreach ($rows as $row) { echo $row->brandId, "\n"; } ``` ### 條件條件允許我們過濾我們想要查詢的記錄集。WHERE子句允許這樣做： ```php <?php // 簡單條件 $phql = 'SELECT * FROM Cars WHERE Cars.name = "Lamborghini Espada"'; $cars = $manager->executeQuery($phql); $phql = 'SELECT * FROM Cars WHERE Cars.price > 10000'; $cars = $manager->executeQuery($phql); $phql = 'SELECT * FROM Cars WHERE TRIM(Cars.name) = "Audi R8"'; $cars = $manager->executeQuery($phql); $phql = 'SELECT * FROM Cars WHERE Cars.name LIKE "Ferrari%"'; $cars = $manager->executeQuery($phql); $phql = 'SELECT * FROM Cars WHERE Cars.name NOT LIKE "Ferrari%"'; $cars = $manager->executeQuery($phql); $phql = 'SELECT * FROM Cars WHERE Cars.price IS NULL'; $cars = $manager->executeQuery($phql); $phql = 'SELECT * FROM Cars WHERE Cars.id IN (120, 121, 122)'; $cars = $manager->executeQuery($phql); $phql = 'SELECT * FROM Cars WHERE Cars.id NOT IN (430, 431)'; $cars = $manager->executeQuery($phql); $phql = 'SELECT * FROM Cars WHERE Cars.id BETWEEN 1 AND 100'; $cars = $manager->executeQuery($phql); ``` 此外，作為PHQL的一部分，準備好的參數會自動轉義輸入數據，從而帶來更多安全性： ```php <?php $phql = 'SELECT * FROM Cars WHERE Cars.name = :name:'; $cars = $manager->executeQuery( $phql, [ 'name' => 'Lamborghini Espada' ] ); $phql = 'SELECT * FROM Cars WHERE Cars.name = ?0'; $cars = $manager->executeQuery( $phql, [ 0 => 'Lamborghini Espada' ] ); ``` ## 插入數據使用PHQL，可以使用熟悉的INSERT語句插入數據： ```php <?php // 插入沒有列 $phql = 'INSERT INTO Cars VALUES (NULL, "Lamborghini Espada", ' . '7, 10000.00, 1969, "Grand Tourer")'; $manager->executeQuery($phql); // 指定要插入的列 $phql = 'INSERT INTO Cars (name, brand_id, year, style) ' . 'VALUES ("Lamborghini Espada", 7, 1969, "Grand Tourer")'; $manager->executeQuery($phql); // 使用占位符插入 $phql = 'INSERT INTO Cars (name, brand_id, year, style) ' . 'VALUES (:name:, :brand_id:, :year:, :style)'; $manager->executeQuery( $phql, [ 'name' => 'Lamborghini Espada', 'brand_id' => 7, 'year' => 1969, 'style' => 'Grand Tourer', ] ); ``` Phalcon不僅將PHQL語句轉換為SQL。執行模型中定義的所有事件和業務規則，就像我們手動創建單個對象一樣。讓我們在模型車上添加一個商業規則。一輛車不能低于$10,000： ```php <?php use Phalcon\Mvc\Model; use Phalcon\Mvc\Model\Message; class Cars extends Model { public function beforeCreate() { if ($this->price < 10000) { $this->appendMessage( new Message('A car cannot cost less than $ 10,000') ); return false; } } } ``` 如果我們在模型Cars中進行了以下 `INSERT` ，則操作將不會成功，因為價格不符合我們實施的業務規則。通過檢查插入的狀態，我們可以打印內部生成的任何驗證消息： ```php <?php $phql = "INSERT INTO Cars VALUES (NULL, 'Nissan Versa', 7, 9999.00, 2015, 'Sedan')"; $result = $manager->executeQuery($phql); if ($result->success() === false) { foreach ($result->getMessages() as $message) { echo $message->getMessage(); } } ``` ## 更新數據更新行與插入行非常相似。您可能知道，更新記錄的指令是更新。更新記錄時，將對每行執行與更新操作相關的事件。 ```php <?php // 更新單個列 $phql = 'UPDATE Cars SET price = 15000.00 WHERE id = 101'; $manager->executeQuery($phql); // 更新多個列 $phql = 'UPDATE Cars SET price = 15000.00, type = "Sedan" WHERE id = 101'; $manager->executeQuery($phql); // 更新多行 $phql = 'UPDATE Cars SET price = 7000.00, type = "Sedan" WHERE brands_id > 5'; $manager->executeQuery($phql); // 使用占位符 $phql = 'UPDATE Cars SET price = ?0, type = ?1 WHERE brands_id > ?2'; $manager->executeQuery( $phql, [ 0 => 7000.00, 1 => 'Sedan', 2 => 5, ] ); ``` `UPDATE` 語句分兩個階段執行更新： * 首先，如果 `UPDATE` 有一個 `WHERE` 子句，它將檢索符合這些條件的所有對象， * 其次，基于查詢的對象，它更新/更改將它們存儲到關系數據庫的請求屬性這種操作方式允許事件，虛擬外鍵和驗證參與更新過程。總之，以下代碼： ```php <?php $phql = 'UPDATE Cars SET price = 15000.00 WHERE id > 101'; $result = $manager->executeQuery($phql); if ($result->success() === false) { $messages = $result->getMessages(); foreach ($messages as $message) { echo $message->getMessage(); } } ``` 有點相當于： ```php <?php $messages = null; $process = function () use (&$messages) { $cars = Cars::find('id > 101'); foreach ($cars as $car) { $car->price = 15000; if ($car->save() === false) { $messages = $car->getMessages(); return false; } } return true; }; $success = $process(); ``` ## 刪除數據刪除記錄時，將對每行執行與刪除操作相關的事件： ```php <?php // 刪除單行 $phql = 'DELETE FROM Cars WHERE id = 101'; $manager->executeQuery($phql); // 刪除多行 $phql = 'DELETE FROM Cars WHERE id > 100'; $manager->executeQuery($phql); // 使用占位符 $phql = 'DELETE FROM Cars WHERE id BETWEEN :initial: AND :final:'; $manager->executeQuery( $phql, [ 'initial' => 1, 'final' => 100, ] ); ``` `DELETE` 操作也分兩個階段執行，如 `UPDATE`。要檢查刪除是否產生任何驗證消息，您應該檢查返回的狀態代碼： ```php <?php // 刪除多行 $phql = 'DELETE FROM Cars WHERE id > 100'; $result = $manager->executeQuery($phql); if ($result->success() === false) { $messages = $result->getMessages(); foreach ($messages as $message) { echo $message->getMessage(); } } ``` ## 使用查詢生成器創建查詢可以使用構建器創建PHQL查詢，而無需編寫PHQL語句，還提供IDE工具： ```php <?php // 獲取整個集合 $robots = $this->modelsManager->createBuilder() ->from('Robots') ->join('RobotsParts') ->orderBy('Robots.name') ->getQuery() ->execute(); // 獲取第一行 $robots = $this->modelsManager->createBuilder() ->from('Robots') ->join('RobotsParts') ->orderBy('Robots.name') ->getQuery() ->getSingleResult(); ``` 這與以下相同： ```php <?php $phql = 'SELECT Robots.* FROM Robots JOIN RobotsParts p ORDER BY Robots.name LIMIT 20'; $result = $manager->executeQuery($phql); ``` 更多生成器的例子： ```php <?php // 'SELECT Robots.* FROM Robots'; $builder->from('Robots'); // 'SELECT Robots.*, RobotsParts.* FROM Robots, RobotsParts'; $builder->from( [ 'Robots', 'RobotsParts', ] ); // 'SELECT * FROM Robots'; $phql = $builder->columns('*') ->from('Robots'); // 'SELECT id FROM Robots'; $builder->columns('id') ->from('Robots'); // 'SELECT id, name FROM Robots'; $builder->columns(['id', 'name']) ->from('Robots'); // 'SELECT Robots.* FROM Robots WHERE Robots.name = 'Voltron''; $builder->from('Robots') ->where("Robots.name = 'Voltron'"); // 'SELECT Robots.* FROM Robots WHERE Robots.id = 100'; $builder->from('Robots') ->where(100); // 'SELECT Robots.* FROM Robots WHERE Robots.type = 'virtual' AND Robots.id > 50'; $builder->from('Robots') ->where("type = 'virtual'") ->andWhere('id > 50'); // 'SELECT Robots.* FROM Robots WHERE Robots.type = 'virtual' OR Robots.id > 50'; $builder->from('Robots') ->where("type = 'virtual'") ->orWhere('id > 50'); // 'SELECT Robots.* FROM Robots GROUP BY Robots.name'; $builder->from('Robots') ->groupBy('Robots.name'); // 'SELECT Robots.* FROM Robots GROUP BY Robots.name, Robots.id'; $builder->from('Robots') ->groupBy(['Robots.name', 'Robots.id']); // 'SELECT Robots.name, SUM(Robots.price) FROM Robots GROUP BY Robots.name'; $builder->columns(['Robots.name', 'SUM(Robots.price)']) ->from('Robots') ->groupBy('Robots.name'); // 'SELECT Robots.name, SUM(Robots.price) FROM Robots GROUP BY Robots.name HAVING SUM(Robots.price) > 1000'; $builder->columns(['Robots.name', 'SUM(Robots.price)']) ->from('Robots') ->groupBy('Robots.name') ->having('SUM(Robots.price) > 1000'); // 'SELECT Robots.* FROM Robots JOIN RobotsParts'; $builder->from('Robots') ->join('RobotsParts'); // 'SELECT Robots.* FROM Robots JOIN RobotsParts AS p'; $builder->from('Robots') ->join('RobotsParts', null, 'p'); // 'SELECT Robots.* FROM Robots JOIN RobotsParts ON Robots.id = RobotsParts.robots_id AS p'; $builder->from('Robots') ->join('RobotsParts', 'Robots.id = RobotsParts.robots_id', 'p'); // 'SELECT Robots.* FROM Robots // JOIN RobotsParts ON Robots.id = RobotsParts.robots_id AS p // JOIN Parts ON Parts.id = RobotsParts.parts_id AS t'; $builder->from('Robots') ->join('RobotsParts', 'Robots.id = RobotsParts.robots_id', 'p') ->join('Parts', 'Parts.id = RobotsParts.parts_id', 't'); // 'SELECT r.* FROM Robots AS r'; $builder->addFrom('Robots', 'r'); // 'SELECT Robots.*, p.* FROM Robots, Parts AS p'; $builder->from('Robots') ->addFrom('Parts', 'p'); // 'SELECT r.*, p.* FROM Robots AS r, Parts AS p'; $builder->from(['r' => 'Robots']) ->addFrom('Parts', 'p'); // 'SELECT r.*, p.* FROM Robots AS r, Parts AS p'; $builder->from(['r' => 'Robots', 'p' => 'Parts']); // 'SELECT Robots.* FROM Robots LIMIT 10'; $builder->from('Robots') ->limit(10); // 'SELECT Robots.* FROM Robots LIMIT 10 OFFSET 5'; $builder->from('Robots') ->limit(10, 5); // 'SELECT Robots.* FROM Robots WHERE id BETWEEN 1 AND 100'; $builder->from('Robots') ->betweenWhere('id', 1, 100); // 'SELECT Robots.* FROM Robots WHERE id IN (1, 2, 3)'; $builder->from('Robots') ->inWhere('id', [1, 2, 3]); // 'SELECT Robots.* FROM Robots WHERE id NOT IN (1, 2, 3)'; $builder->from('Robots') ->notInWhere('id', [1, 2, 3]); // 'SELECT Robots.* FROM Robots WHERE name LIKE '%Art%'; $builder->from('Robots') ->where('name LIKE :name:', ['name' => '%' . $name . '%']); // 'SELECT r.* FROM Store\Robots WHERE r.name LIKE '%Art%'; $builder->from(['r' => 'Store\Robots']) ->where('r.name LIKE :name:', ['name' => '%' . $name . '%']); ``` ### 綁定參數查詢構建器中的綁定參數可以在執行時一次構造或過去查詢時設置： ```php <?php // 在查詢構造中傳遞參數 $robots = $this->modelsManager->createBuilder() ->from('Robots') ->where('name = :name:', ['name' => $name]) ->andWhere('type = :type:', ['type' => $type]) ->getQuery() ->execute(); // 在查詢執行中傳遞參數 $robots = $this->modelsManager->createBuilder() ->from('Robots') ->where('name = :name:') ->andWhere('type = :type:') ->getQuery() ->execute(['name' => $name, 'type' => $type]); ``` ## 在PHQL中禁止文字可以在PHQL中禁用文字，這意味著不允許直接在PHQL字符串中使用字符串，數字和布爾值。如果創建了PHQL語句，并在其上嵌入外部數據，則可以打開應用程序以進行潛在的SQL注入： ```php <?php $login = 'voltron'; $phql = "SELECT * FROM Models\Users WHERE login = '$login'"; $result = $manager->executeQuery($phql); ``` 如果 `$login` 更改為 `' OR '' = '`，則生成的PHQL為： ```sql SELECT * FROM Models\Users WHERE login = '' OR '' = '' ``` 無論數據庫中存儲的是什么，都始終`true`。如果不允許文字，則可以將字符串用作PHQL語句的一部分，因此將拋出異常，迫使開發人員使用綁定參數。可以用以下安全方式編寫相同的查詢： ```php <?php $type = 'virtual'; $phql = 'SELECT Robots.* FROM Robots WHERE Robots.type = :type:'; $result = $manager->executeQuery( $phql, [ 'type' => $type, ] ); ``` 您可以通過以下方式禁用文字： ```php <?php use Phalcon\Mvc\Model; Model::setup( [ 'phqlLiterals' => false ] ); ``` 即使允許或不允許文字，也可以使用綁定參數。禁止它們只是開發人員可以在Web應用程序中采取的另一個安全決策。 ## 轉義保留字 PHQL有一些保留字，如果要將它們中的任何一個用作屬性或模型名稱，則需要使用跨數據庫轉義分隔符 `[` 和 `]`來轉義這些字： ```php <?php $phql = 'SELECT * FROM [Update]'; $result = $manager->executeQuery($phql); $phql = 'SELECT id, [Like] FROM Posts'; $result = $manager->executeQuery($phql); ``` 根據當前運行應用程序的數據庫系統，分隔符會動態轉換為有效的分隔符。 ## PHQL生命周期作為一種高級語言，PHQL使開發人員能夠個性化和定制不同方面，以滿足他們的需求。以下是每個執行的PHQL語句的生命周期： * 解析PHQL并將其轉換為中間表示（IR），它獨立于數據庫系統實現的SQL * 根據與模型關聯的數據庫系統將IR轉換為有效的SQL * PHQL語句被解析一次并緩存在內存中。進一步執行相同的語句會導致執行速度稍快 ## 使用原始SQL 數據庫系統可以提供PHQL不支持的特定SQL擴展，在這種情況下，原始SQL可能是合適的： ```php <?php use Phalcon\Mvc\Model; use Phalcon\Mvc\Model\Resultset\Simple as Resultset; class Robots extends Model { public static function findByCreateInterval() { // 原始SQL語句 $sql = 'SELECT * FROM robots WHERE id > 0'; // 基礎模型 $robot = new Robots(); // 執行查詢 return new Resultset( null, $robot, $robot->getReadConnection()->query($sql) ); } } ``` 如果原始SQL查詢在您的應用程序中很常見，則可以在模型中添加通用方法： ```php <?php use Phalcon\Mvc\Model; use Phalcon\Mvc\Model\Resultset\Simple as Resultset; class Robots extends Model { public static function findByRawSql($conditions, $params = null) { // 原始SQL語句 $sql = 'SELECT * FROM robots WHERE $conditions'; // 基礎模型 $robot = new Robots(); // 執行查詢 return new Resultset( null, $robot, $robot->getReadConnection()->query($sql, $params) ); } } ``` 上面的`findByRawSql`可以用如下： ```php <?php $robots = Robots::findByRawSql( 'id > ?', [ 10 ] ); ``` ## 故障排除使用PHQL時要記住的一些事項： * 類是區分大小寫的，如果沒有使用與創建時相同的名稱定義類，這可能會導致在具有區分大小寫的文件系統（如Linux）的操作系統中出現意外行為。 * 必須在連接中定義正確的字符集才能成功綁定參數。 * 別名類不會被完整的命名空間類替換，因為這只發生在PHP代碼中，而不是在字符串內。 * 如果啟用了列重命名，請避免使用與要重命名的列具有相同名稱的列別名，這可能會使查詢解析程序混淆。