{"id":1629,"date":"2020-06-03T21:48:34","date_gmt":"2020-06-03T13:48:34","guid":{"rendered":"http:\/\/bloo.heing.fun\/?p=1629"},"modified":"2020-06-03T21:49:34","modified_gmt":"2020-06-03T13:49:34","slug":"mysql-%e5%87%bd%e6%95%b0-%e8%bf%87%e7%a8%8b","status":"publish","type":"post","link":"https:\/\/bloo.heing.fun\/?p=1629","title":{"rendered":"mysql \u51fd\u6570 \u8fc7\u7a0b"},"content":{"rendered":"\n

\u51fd\u6570\u7684csdn\u535a\u5ba2\u6559\u7a0b<\/a><\/p>\n\n\n\n

\u51fd\u6570\u793a\u4f8b\uff1a<\/p>\n\n\n\n

DELIMITER $$\ncreate function periodtos(kini int)\nreturns varchar(30)\nno sql\ndeterministic\nbegin\n\tdeclare mout varchar(30);\n\tcase kini\n\t\twhen 1 then\n\t\t\tset mout='\u4e0a\u5348';\n\t\twhen 2 then\n\t\t\tset mout='\u4e0b\u5348';\n\t\telse\n\t\t\tset mout='\u672a\u77e5';\n\tend case;\n\treturn mout;\nend$$\nDELIMITER ;<\/code><\/pre>\n\n\n\n
\u51fd\u6570\u8bf4\u660e1\uff1a<\/p>\n\n\n\n
  • When you create a stored function, you must declare either that it is deterministic or that it does not modify data. Otherwise, it may be unsafe for data recovery or replication.<\/li>
  • By default, for a CREATE FUNCTION statement to be accepted, at least one of DETERMINISTIC, NO SQL, or READS SQL DATA must be specified explicitly. Otherwise an error occurs<\/li><\/ul>\n\n\n\n
    \u51fd\u6570\u8bf4\u660e2\uff1a<\/p>\n\n\n\n
    DETERMINISTIC<\/strong> A routine is considered \u201cdeterministic\u201d if it always produces the same result for the same input parameters and NOT DETERMINISTIC otherwise. This is mostly used with string or math processing, but not limited to that.<\/p>\n\n\n\n
    NOT DETERMINISTIC<\/strong> Opposite of “DETERMINISTIC”. “If neither DETERMINISTIC nor NOT DETERMINISTIC is given in the routine definition, the default is NOT DETERMINISTIC. To declare that a function is deterministic, you must specify DETERMINISTIC explicitly.<\/em>“. So it seems that if no statement is made, MySQl will treat the function as “NOT DETERMINISTIC”. This statement from manual is in contradiction with other statement from another area of manual which tells that: ” When you create a stored function, you must declare either that it is deterministic or that it does not modify data. Otherwise, it may be unsafe for data recovery or replication. By default, for a CREATE FUNCTION statement to be accepted, at least one of DETERMINISTIC, NO SQL, or READS SQL DATA must be specified explicitly. Otherwise an error occurs<\/em>“<\/p>\n\n\n\n
    I personally got error in MySQL 5.5 if there is no declaration, so i always put at least one declaration of “DETERMINISTIC”, “NOT DETERMINISTIC”, “NO SQL” or “READS SQL DATA” regardless other declarations i may have.<\/p>\n\n\n\n
    READS SQL DATA<\/strong> This explicitly tells to MySQL that the function will ONLY read data from databases, thus, it does not contain instructions that modify data, but it contains SQL instructions that read data (e.q. SELECT).<\/p>\n\n\n\n
    MODIFIES SQL DATA<\/strong> This indicates that the routine contains statements that may write data (for example, it contain UPDATE, INSERT, DELETE or ALTER instructions).<\/p>\n\n\n\n
    NO SQL<\/strong> This indicates that the routine contains no SQL statements.<\/p>\n\n\n\n
    CONTAINS SQL<\/strong> This indicates that the routine contains SQL instructions, but does not contain statements that read or write data. This is the default if none of these characteristics is given explicitly. Examples of such statements are SELECT NOW(), SELECT 10+@b, SET @x = 1 or DO RELEASE_LOCK(‘abc’), which execute but neither read nor write data.<\/p>\n\n\n\n
    Note that there are MySQL functions that are not deterministic safe, such as: NOW(), UUID(), etc, which are likely to produce different results on different machines, so a user function that contains such instructions must be declared as NOT DETERMINISTIC. Also, a function that reads data from an unreplicated schema is clearly NONDETERMINISTIC. *<\/p>\n\n\n\n
    \n\n\n\n
    \u5176\u4ed6\uff1a\u5220\u9664\u51fd\u6570: drop function f1;<\/p>\n\n\n\n
    \u5220\u9664\u8fc7\u7a0b: drop procedure p1;<\/p>\n","protected":false},"excerpt":{"rendered":"
    \u51fd\u6570\u7684csdn\u535a\u5ba2\u6559\u7a0b \u51fd\u6570\u793a\u4f8b\uff1a \u51fd\u6570\u8bf4\u660e1\uff1a When you create a stored funct […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[75,36],"tags":[],"class_list":["post-1629","post","type-post","status-publish","format-standard","hentry","category-75","category-36"],"_links":{"self":[{"href":"https:\/\/bloo.heing.fun\/index.php?rest_route=\/wp\/v2\/posts\/1629","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bloo.heing.fun\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bloo.heing.fun\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bloo.heing.fun\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/bloo.heing.fun\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1629"}],"version-history":[{"count":2,"href":"https:\/\/bloo.heing.fun\/index.php?rest_route=\/wp\/v2\/posts\/1629\/revisions"}],"predecessor-version":[{"id":1631,"href":"https:\/\/bloo.heing.fun\/index.php?rest_route=\/wp\/v2\/posts\/1629\/revisions\/1631"}],"wp:attachment":[{"href":"https:\/\/bloo.heing.fun\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1629"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bloo.heing.fun\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1629"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bloo.heing.fun\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1629"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}