Français
Français
Le deuxième paramètre de mysqli_stmt :: bind_param est utilisé pour spécifier le type de la variable de liaison, qui peut être les caractères suivants:
I : entier
D : Type flottant double précision (double)
S : String (String)
B : données binaires (blob)
L'une des erreurs de conversion de type les plus courantes est que le type de variable transmis à bind_param est incohérent avec le type attendu. Par exemple, si vous vous attendez à un entier ( i ) dans une instruction SQL mais que vous transmettez en fait une variable de type de chaîne, MySQL convertira la chaîne en 0 ou une erreur se produit.
Exemple d'erreur :
<span><span><span class="hljs-variable">$stmt</span></span><span> = </span><span><span class="hljs-variable">$mysqli</span></span><span>-></span><span><span class="hljs-title function_ invoke__">prepare</span></span><span>(</span><span><span class="hljs-string">"SELECT * FROM users WHERE age = ?"</span></span><span>);
</span><span><span class="hljs-variable">$age</span></span><span> = </span><span><span class="hljs-string">"25"</span></span><span>; </span><span><span class="hljs-comment">// Type de chaîne</span></span><span>
</span><span><span class="hljs-variable">$stmt</span></span><span>-></span><span><span class="hljs-title function_ invoke__">bind_param</span></span><span>(</span><span><span class="hljs-string">"i"</span></span><span>, </span><span><span class="hljs-variable">$age</span></span><span>); </span><span><span class="hljs-comment">// Erreur de type de liaison，Ce devrait être un entier（i）</span></span><span>
</span><span><span class="hljs-variable">$stmt</span></span><span>-></span><span><span class="hljs-title function_ invoke__">execute</span></span><span>();
</span></span> Solution :
Assurez-vous que le type de variable lié est cohérent avec le type attendu d'instruction de requête SQL. Si les types de variables ne correspondent pas, vous pouvez d'abord effectuer la conversion de type, puis vous lier.
<span><span><span class="hljs-variable">$age</span></span><span> = </span><span><span class="hljs-number">25</span></span><span>; </span><span><span class="hljs-comment">// Assurez-vous que c'est un type entier</span></span><span>
</span><span><span class="hljs-variable">$stmt</span></span><span>-></span><span><span class="hljs-title function_ invoke__">bind_param</span></span><span>(</span><span><span class="hljs-string">"i"</span></span><span>, </span><span><span class="hljs-variable">$age</span></span><span>); </span><span><span class="hljs-comment">// Entier de liaison</span></span><span>
</span></span>La fonction mysqli_stmt :: bind_param est spéciale lors du traitement des valeurs nulles . NULL dans PHP doit être explicitement lié et ne peut pas être transmis directement à Bind_param . Sinon, cela entraînera une défaillance de l'exécution ou un comportement incorrect.
Exemple d'erreur :
<span><span><span class="hljs-variable">$stmt</span></span><span> = </span><span><span class="hljs-variable">$mysqli</span></span><span>-></span><span><span class="hljs-title function_ invoke__">prepare</span></span><span>(</span><span><span class="hljs-string">"INSERT INTO users (name, age) VALUES (?, ?)"</span></span><span>);
</span><span><span class="hljs-variable">$name</span></span><span> = </span><span><span class="hljs-string">"John"</span></span><span>;
</span><span><span class="hljs-variable">$age</span></span><span> = </span><span><span class="hljs-literal">null</span></span><span>; </span><span><span class="hljs-comment">// nullvaleur</span></span><span>
</span><span><span class="hljs-variable">$stmt</span></span><span>-></span><span><span class="hljs-title function_ invoke__">bind_param</span></span><span>(</span><span><span class="hljs-string">"si"</span></span><span>, </span><span><span class="hljs-variable">$name</span></span><span>, </span><span><span class="hljs-variable">$age</span></span><span>); </span><span><span class="hljs-comment">// erreur，Impossible de lier directementNULL</span></span><span>
</span><span><span class="hljs-variable">$stmt</span></span><span>-></span><span><span class="hljs-title function_ invoke__">execute</span></span><span>();
</span></span> Solution :
Lors de la liaison des valeurs NULL , Bind_param ne prend pas en charge la liaison directe de NULL , il doit donc être converti au format approprié ou utiliser une version différente de Bind_param . La méthode la plus courante consiste à utiliser MySQLI_STMT :: Send_long_data , mais c'est plus lourd pour gérer les valeurs nulles .
<span><span><span class="hljs-keyword">if</span></span><span> (</span><span><span class="hljs-variable">$age</span></span><span> === </span><span><span class="hljs-literal">null</span></span><span>) {
</span><span><span class="hljs-variable">$age</span></span><span> = </span><span><span class="hljs-number">0</span></span><span>; </span><span><span class="hljs-comment">// 或者根据实际需求设置一个默认valeur</span></span><span>
}
</span><span><span class="hljs-variable">$stmt</span></span><span>-></span><span><span class="hljs-title function_ invoke__">bind_param</span></span><span>(</span><span><span class="hljs-string">"si"</span></span><span>, </span><span><span class="hljs-variable">$name</span></span><span>, </span><span><span class="hljs-variable">$age</span></span><span>); </span><span><span class="hljs-comment">// Lier默认valeur</span></span><span>
</span><span><span class="hljs-variable">$stmt</span></span><span>-></span><span><span class="hljs-title function_ invoke__">execute</span></span><span>();
</span></span>Parfois, nous rencontrons des nombres qui passent sous forme de cordes, ou l'inverse. Bien que MySQL effectuera automatiquement des conversions de types dans certains cas, cela peut toujours entraîner des problèmes de performances ou un comportement imprévisible.
Exemple d'erreur :
<span><span><span class="hljs-variable">$stmt</span></span><span> = </span><span><span class="hljs-variable">$mysqli</span></span><span>-></span><span><span class="hljs-title function_ invoke__">prepare</span></span><span>(</span><span><span class="hljs-string">"SELECT * FROM products WHERE price = ?"</span></span><span>);
</span><span><span class="hljs-variable">$price</span></span><span> = </span><span><span class="hljs-number">199.99</span></span><span>; </span><span><span class="hljs-comment">// 价格是浮动数valeur</span></span><span>
</span><span><span class="hljs-variable">$stmt</span></span><span>-></span><span><span class="hljs-title function_ invoke__">bind_param</span></span><span>(</span><span><span class="hljs-string">"s"</span></span><span>, </span><span><span class="hljs-variable">$price</span></span><span>); </span><span><span class="hljs-comment">// erreur，Devrait être utiliséd（double）taper</span></span><span>
</span><span><span class="hljs-variable">$stmt</span></span><span>-></span><span><span class="hljs-title function_ invoke__">execute</span></span><span>();
</span></span> Solution :
Si vous passez une valeur flottante, assurez-vous d'utiliser le type d (double) au lieu de S (chaîne). Cela garantit non seulement la réussite de type de données, mais améliore également l'efficacité de la requête.
<span><span><span class="hljs-variable">$stmt</span></span><span>-></span><span><span class="hljs-title function_ invoke__">bind_param</span></span><span>(</span><span><span class="hljs-string">"d"</span></span><span>, </span><span><span class="hljs-variable">$price</span></span><span>); </span><span><span class="hljs-comment">// Lier浮动数valeur</span></span><span>
</span><span><span class="hljs-variable">$stmt</span></span><span>-></span><span><span class="hljs-title function_ invoke__">execute</span></span><span>();
</span></span>Bind_param ne prend en charge que des types scalaires simples (valeur intégrale, flottante, chaîne, données binaires). Si un tableau ou un objet est passé, Bind_param échouera. Par conséquent, avant de passer des types de données complexes, les données doivent être prétraitées.
Exemple d'erreur :
<span><span><span class="hljs-variable">$stmt</span></span><span> = </span><span><span class="hljs-variable">$mysqli</span></span><span>-></span><span><span class="hljs-title function_ invoke__">prepare</span></span><span>(</span><span><span class="hljs-string">"INSERT INTO users (preferences) VALUES (?)"</span></span><span>);
</span><span><span class="hljs-variable">$preferences</span></span><span> = [</span><span><span class="hljs-string">"color"</span></span><span> => </span><span><span class="hljs-string">"blue"</span></span><span>, </span><span><span class="hljs-string">"size"</span></span><span> => </span><span><span class="hljs-string">"L"</span></span><span>]; </span><span><span class="hljs-comment">// 数组taper</span></span><span>
</span><span><span class="hljs-variable">$stmt</span></span><span>-></span><span><span class="hljs-title function_ invoke__">bind_param</span></span><span>(</span><span><span class="hljs-string">"s"</span></span><span>, </span><span><span class="hljs-variable">$preferences</span></span><span>); </span><span><span class="hljs-comment">// erreur，Les tableaux ne peuvent pas être liés directement</span></span><span>
</span><span><span class="hljs-variable">$stmt</span></span><span>-></span><span><span class="hljs-title function_ invoke__">execute</span></span><span>();
</span></span> Solution :
Avant de passer un tableau ou un objet, il est généralement nécessaire de le sérialiser en une chaîne JSON ou un autre format.
<span><span><span class="hljs-variable">$preferences</span></span><span> = </span><span><span class="hljs-title function_ invoke__">json_encode</span></span><span>([</span><span><span class="hljs-string">"color"</span></span><span> => </span><span><span class="hljs-string">"blue"</span></span><span>, </span><span><span class="hljs-string">"size"</span></span><span> => </span><span><span class="hljs-string">"L"</span></span><span>]);
</span><span><span class="hljs-variable">$stmt</span></span><span>-></span><span><span class="hljs-title function_ invoke__">bind_param</span></span><span>(</span><span><span class="hljs-string">"s"</span></span><span>, </span><span><span class="hljs-variable">$preferences</span></span><span>); </span><span><span class="hljs-comment">// LierJSONChaîne</span></span><span>
</span><span><span class="hljs-variable">$stmt</span></span><span>-></span><span><span class="hljs-title function_ invoke__">execute</span></span><span>();
</span></span>Une erreur de conversion de type sera également augmentée si le nombre lié dépasse la plage maximale du type spécifié. Pour les entiers ( i ), il varie généralement de -2 147 483 648 à 2 147 483 647 . Pour les valeurs flottantes ( D ), la plage est beaucoup plus grande, mais il y a toujours une limite supérieure.
Exemple d'erreur :
<span><span><span class="hljs-variable">$stmt</span></span><span> = </span><span><span class="hljs-variable">$mysqli</span></span><span>-></span><span><span class="hljs-title function_ invoke__">prepare</span></span><span>(</span><span><span class="hljs-string">"INSERT INTO large_numbers (value) VALUES (?)"</span></span><span>);
</span><span><span class="hljs-variable">$value</span></span><span> = </span><span><span class="hljs-number">1000000000000</span></span><span>; </span><span><span class="hljs-comment">// Trop grand nombre</span></span><span>
</span><span><span class="hljs-variable">$stmt</span></span><span>-></span><span><span class="hljs-title function_ invoke__">bind_param</span></span><span>(</span><span><span class="hljs-string">"i"</span></span><span>, </span><span><span class="hljs-variable">$value</span></span><span>); </span><span><span class="hljs-comment">// erreur，Les nombres sont hors de portée entier</span></span><span>
</span><span><span class="hljs-variable">$stmt</span></span><span>-></span><span><span class="hljs-title function_ invoke__">execute</span></span><span>();
</span></span> Solution :
Si le numéro passé est en dehors de la plage entière, vous pouvez utiliser le type flottant ( D ) pour éviter le débordement ou le convertir en une plage appropriée.
<span><span><span class="hljs-variable">$stmt</span></span><span>-></span><span><span class="hljs-title function_ invoke__">bind_param</span></span><span>(</span><span><span class="hljs-string">"d"</span></span><span>, </span><span><span class="hljs-variable">$value</span></span><span>); </span><span><span class="hljs-comment">// 使用双精度浮动数valeur</span></span><span>
</span><span><span class="hljs-variable">$stmt</span></span><span>-></span><span><span class="hljs-title function_ invoke__">execute</span></span><span>();
</span></span>
Étiquettes associées:
mysqli_stmt
